Difference Between Delete and Truncate

.Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

. Truncate table also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

. Truncate table is functionally identical to delete statement with no "where clause" both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

. Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

. In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

. If you want to retain the identity counter, use delete statement instead.

. If you want to remove table definition and its data, use the drop table statement.

. You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

SQL Server System Databases

# Master

* Purpose – Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
* Prominent Functionality
o Per instance configurations
o Databases residing on the instance
o Files for each database
o Logins
o Linked\Remote servers
o Endpoints
* Additional Information
o The first database in the SQL Server startup process
o In SQL Server 2005, needs to reside in the same directory as the Resource database

# Resource

* Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
* Prominent Functionality
o System object definition
* Additional Information
o Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
o Prior to SQL Server 2005 the system related data was stored in the master database
o Read-only database that is not accessible via the SQL Server 2005 tool set
o The database ID for the Resource database is 32767
o The Resource database does not have an entry in master.sys.databases

# TempDB

* Purpose – Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
* Prominent Functionality
o Manage temporary objects listed in the purpose above
* Additional Information
o Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state

# Model

* Purpose – Template database for all user defined databases
* Prominent Functionality
o Objects
o Columns
o Users
* Additional Information
o User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
o The database configurations such as the recovery model for the Model database are applied to future user defined databases

# MSDB

* Purpose – Primary database to manage the SQL Server Agent configurations
* Prominent Functionality
o SQL Server Agent Jobs, Operators and Alerts
o DTS Package storage in SQL Server 7.0 and 2000
o SSIS Package storage in SQL Server 2005
* Additional Information
o Provides some of the configurations for the SQL Server Agent service
o For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database

ADO: Execute a sql script using vb.net

http://dotnet.itags.org/dotnet-ado/82398/

HELP LINKS

SQL Server backup utility using VB.Net and SQL-DMO

http://www.c-sharpcorner.com/UploadFile/shabdarghata/sql-server-backup-utility-sql-dmo02042008134007PM/sql-server-backup-utility-sql-dmo.aspx

http://www.c-sharpcorner.com/uploadfile/shivprasadk/1420/default.aspx?login=true&user=ravuripradeep

DDL Triggers in SQL Server 2005

DDL Triggers in SQL Server 2005



We all are familiar with DML (Data Manipulation Language) triggers which can be invoked after any DML operations (used for update, insert and delete commands). We can achieve auditing functionality using DML triggers but this does not help us in auditing if someone has altered the definition of the tables or dropped the tables etc.. We can achieve this by using DDL (Data Definition Language) triggers introduced in SQL Server 2005.

As the name suggests, DDL triggers do not get fired for any DML operations but get fired for CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS DDL statements. Some of the system stored procedures which perform CREATE like operations, also fire the DDL trigger. One exception is sp_rename. Sp_rename procedure does not invoke DDL trigger even though it performs the operation of renaming the object. Most of the time, DDL triggers are used to prevent modification in the database or to audit the DDL changes in the database.

DDL triggers can be created at the database level for a specific database or at the server/instance level. For creating trigger at the database level, we have to use the ‘ON DATABASE’ clause and for creating trigger at the server/instance level we have to use the ‘ON ALL SERVER’ clause. When created at the server level, it keeps track of DDL changes for any database on that specific instance.

Let us create a table first.

CREATE TABLE dbo.AUDIT_EVENT
(
AUDIT_EVENT_ID INT IDENTITY(1,1),
EVENT_TEXT VARCHAR(MAX),
ACTION_LOGIN VARCHAR(100),
ACTION_DATE DATETIME,
CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY(AUDIT_EVENT_ID)
)
GO

In the above table, we will record the information pertaining to any DDL operation. Let us create trigger to record the changes at database level.

CREATE TRIGGER AUDIT_TRIG
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
INSERT INTO dbo.Audit_Event(Event_Text, Action_Login, Action_date)
SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]‘,’nvarchar(max)’), original_login(), getDate()
GO

In the trigger shown above, we are using the EVENTDATA() function to obtain the actual text. We will cover more about EVENTDATA() function in future blog,. We have adopted the idea of using EVENTDATA() function from BOL example. Once trigger is in place, we can start testing it by creating, altering and dropping the table. Let us try to create table first. We will also alter it to add a new column and then we will drop it.

CREATE TABLE TEST(ID INT IDENTITY(1,1))
GO
ALTER TABLE TEST ADD TEST_DESC VARCHAR(30)
GO
DROP TABLE TEST
GO

For all of the above statements, once the statement is executed successfully, trigger will get fired. Let us check our audit table now.

SELECT Event_Text,Action_Login, Action_Date FROM AUDIT_EVENT
GO

Here is the result set:

Event_Text Action_login Action_Date

------------------------------------------- ------------- -----------------------

CREATE TABLE TEST(ID INT IDENTITY(1,1)) sa 2007-09-26 13:43:57.177

ALTER TABLE TEST ADD TEST_DESC VARCHAR(30) sa 2007-09-26 13:43:57.223

DROP TABLE TEST sa 2007-09-26 13:43:57.223

From the above result set, we know what command got executed, at what time and by whom. This prevents unwanted surprises during the development phase and in addition, we can keep track of database changes made within the application. Similar to the example shown above, we can also create trigger for CREATE_PROCEDURE, DROP_PROCEDURE event types to audit the procedure changes.

What are ACID Rules of transaction in a database? Atomicity, Consistency, Isolation, Durability

What are ACID Rules of transaction in a database? Atomicity, Consistency, Isolation, Durability

The ACID rules of transaction in any database assure the reliability of data in all transactions in the database.

Atomicity - this rule states that either the complete transaction takes place, or none. Even if a part of a transaction fails to work, the complete transaction will fail.

Consistency - this rule ensures that the database is stable, before and after the transaction, even if a transaction fails.

Isolation - this rule states that when a process is going on in a transaction, the data remains in isolation of other entities in the database.

Durability - this rule states that when a transaction completes successfully, it remains in stable state and is persisted in the database.

What is the difference between Host_Name() and ServerProperty('MachineName') Functions ?

The Host_Name() and ServerProperty('MachineName') Function will return the System Name(Machine Name).

But the difference is ...

Host_Name() will return the Client Machine name.

ServerProperty('MachineName') will return Server Machine name.

ie: CLIENT SQL Server tools only installed on "Computer1" (SQL Server Management Studio) - Connectivity tools only

SERVER SQL Server tools installed on "Computer2" (Databases, SQL Server Services, SQL Server Management Studio, Profiler,...Etc.,)

Now You are the "Client machine (Computer1)", You want to access the Database from the "Server Machine (Computer2)".


Client Machine (Computer1):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer1, Computer2

Server Machine (Computer2):

Select Host_Name(), ServerProperty('MachineName')

Result:
Computer2, Computer2

Back up a single table in SQL Server

Right-click database > tasks > generate sql scripts, then select the table and on the scripting options page, select advanced, choose "schema and data" in types of data to script.


I think this option is available only in SQL Server 2008. I dont think SQL Server 2005 or earlier versions have this feature.