Difference between TRUNCATE, DELETE and DROP commands in SQL SERVER

                                     Hi Friends, in this article I will explain about the Difference between TRUNCATE, DELETE and DROP commands in SQL SERVER.For many interviews this question is frequently asked.

First of all we know those individually after that we will know differences.

Truncate:

TRUNCATE Command  removes all the data or all rows from a table.
·         We cannot use where clause with Truncate command.
·         The operation cannot be rolled back and no triggers will be fired because it does not log individual rows.
·          As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
·         It frees the space containing the table.
·         Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
·         This is a DDL command as its resets identity columns, deallocates data pages and empties them for use of other objects in the database.

Syntax:

TRUNCATE TABLE Employee Table truncated. SELECT COUNT(*) FROM Employee   COUNT(*) ----------------          0

For more information from Microsoft then click on the below link.

http://msdn.microsoft.com/en-us/library/ms177570.aspx

Delete:

·         The DELETE Command removes some rows or all the rows  from a table.
·          A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.
·         After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
·          Note that this operation will cause all DELETE triggers on the table to fire.
·         It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
·         This is a DML command as it is just used to manipulate/modify the table data. It does not change the property or structure of a table.

Syntax:

DELETE FROM TABLE Employee WHERE EMPno=1 1 row(s) deleted. DELETE FROM TABLE Employee 4 row(s) deleted. SELECT COUNT(*) FROM Employee   COUNT(*) -----------------          0

For more information from Microsoft then click on the below link.

http://msdn.microsoft.com/en-us/library/ms189835.aspx

Drop:

·         The DROP Command removes a table from the database.
·         All the tables' rows, indexes and privileges will also be removed.
·         Cannot drop a table that is referenced by any Foreign Key constraint.
·         No DML triggers will be fired. The operation cannot be rolled back.
·         Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.

Syntax:

DROP TABLE Employee Table dropped.

For more information from Microsoft then click on the below link.

http://msdn.microsoft.com/en-us/library/ms173790.aspx

Go to another interview question how to send email using Gmail in VB.NET or C#.