What is the Difference between TRUNCATE and DELETE in SQL Server? – Interview Question of the Week #118

Question: What is the Difference between TRUNCATE and DELETE in SQL Server?

Answer: There are some questions which never gets old. This one of the such questions. I am also surprised to see quite a few people do not know the difference between this two command.

Let us quickly see the difference between them.

What is the Difference between TRUNCATE and DELETE in SQL Server? - Interview Question of the Week #118 truncatevsdelete-800x413

Solarwinds

DIFFERENCES:

TRUNCATE:

  • It is a DDL command
  • It does not support WHERE clause/condition
  • Removes all the data all the time
  • Faster than DELETE as it locks entire table
  • It removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log
  • It does not activate triggers
  • Table identity column is reset to seed value

Syntax: 

TRUNCATE TABLE TableName

DELETE:

  • It is DML command
  • It supports WHERE clause/condition
  • Removes data based on conditions specified in the WHERE clause (removes all the data if there is no WHERE clause)
  • Slower than TRUNCATE as it takes row level locks
  • It removes rows one at a time and records an entry in the transaction log for each deleted row
  • It does activate triggers
  • Table identity column is not reset

Syntax:

DELETE FROM TableName
WHERE ColName = ‘YourCondition’

Well, those are the major differences.

Please Note: Truncate and Delete both are logged operations and both can be rolled back when they are within transactions. It is myth that Truncate is not logged operations. It is indeed logged operations and it locks page level deallocations.

Here are few additional blog posts which I have written about this subject earlier.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Solarwinds
, , ,
Previous Post
How to Change Owner of Database in SQL SERVER? – Interview Question of the Week #117
Next Post
Why SELECT HOST_NAME() Returns Empty String? – Interview Question of the Week #119

Related Posts

4 Comments. Leave new

Leave a Reply

Menu