DELETE and TRUNCATE - Difference and Comparison in Microsoft SQL Server:

I love to write an article about differences and comparison as it is very useful to make your concept clear and also helpful in interview as many interviewers would like know the differences and comparison from the candidate. It proves candidate’s knowledge and his concept about subject.

I have written many articles for differences and comparisons. I would like to list few of them for reference here before we move to our core subject of the article.

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server.html

Difference between SQL Server 2000 and 2005
http://ritesh-a-shah.blogspot.com/2009/03/difference-between-microsoft-sql-server_16.html

Difference between SET and SELECT statement
http://ritesh-a-shah.blogspot.com/2009/03/set-and-select-in-sql-server-2005.html

Difference between EXEC and sp_ExecuteSQL
http://ritesh-a-shah.blogspot.com/2009/03/spexecutesql-and-execexecute-in-sql.html

Difference between BULK INSERT and BULK COPY (BCP)
http://ritesh-a-shah.blogspot.com/2009/03/bulk-insert-bulk-copy-or-bcp-difference.html

And now I am going to describe difference between DELETE and TRUNCATE

First of all let me start by these command’s type. DELETE is a DML command whereas TRUNCATE is a DDL command.

When you use DELETE command, SQL Server engine first move all data to the Rollback tablespace and then delete from the table. In this case disk space will not be immediately release. In short DELETE is a logged transaction and it used to write everything in log whereas TRUNCATE is completely by pass the log system and remove data right from the table so it is faster than DELETE.

If you wish to DELETE data from table based on condition than I recommend using DELETE as TRUNCATE will empty your table. You won’t get a single row in your table whereas we can use WHERE condition with DELETE so that you can remove only those records which you don’t really need.

Since TRUCATE is by passing the log system, you will not get any trigger in action set on the table whereas you will get delete trigger in action when you use DELETE command.

TRUNCATE command will reset your IDENTITY key whereas DELETE command won’t do it for you. In this manner TRUNCATE = DROP TABLE + CREATE TABLE or TRUNCATE=DELETE TABLE + COMMIT TRAN.

Above differences are specific to Microsoft SQL Server but it more or less works same in ORACLE, PostgreSQL and mySQL.

Reference: Ritesh Shah