This article is about comparing delete Vs truncate in SQL server, It is one of the most discussed topic on all SQL server forums, So there is no point in just discussing their basic difference, this article will be an elaborate one with supporting explanations.
Both delete and truncate command is used to for removing records from tables, the main difference between them lies in the way they perform this operation.
Delete Vs Truncate
Difference between delete and truncate can be summarized as below
|Where Condition||support||does not support|
|Reset Identity Column||no||yes|
|Acquired lock||row lock||table and page lock|
|Transaction log||for each deleted row||one log indicating deallocation of page|
|Performance(Speed of execution)||slow||much faster than Delete|
Their first difference is their command type itself,
Delete is a DML Command while truncate is a DDL Command
DML (Data Manipulation Language) commands deals with the manipulation of data like retrieve,modify,delete,insert and update while DDL (Data Definition Language) commands are used to create or modify structure of database objects.
Q : Why is Truncate a DDL Command ?
Ans : Even though truncate statement manipulate table data by removing all table rows. it is considered as DDL command.
It is because of its following after effect
– Deallocates space used by the table
– Reset identity column to its Seed value.
Truncate operation keeps table structure and its column,constraints,indexes as it is.
So truncate operation is like resetting a table to its empty state, hence it is considered as DDL statement/command.
You will be convinced with these points while reading remaing points of this article.
Statements in Action
Let me remind you the basic sql query syntax of Delete and Truncate
Truncate will remove all rows from the table while in case of delete operation, it removes one or more records that matches expression in WHERE condition will be removed.
WHERE condition can’t be used in truncate statement
Resetting of Identity Column
In SQL server table we can specify identity column like
Suppose seed is 1001 and increment/seed is 1 then initial value of the column will 1001 then 1002,1003 and so on, If we add 50 records into the table next value of this identity column will be 1051.
Now if we remove all rows using truncate and delete statements like below
Identity column of truncated table will be reset to it’s seed value that is 1001 and that of deleted table remains same.
so if we add additional row into the table,
identity column value will be 1001 and that of deleted table is 1051.
How it works ?
Delete operation removes one row at a time, for every deleted row transaction log will created in transaction log.
While truncate operation deallocates data pages of the table, hence the storage can be used for other tables also.Only these deallocation is stored in log file.
SQL Server Lock
You should be aware of sql server locks to understand next point.it is a mechanism to prevent other operations from making changes to data until the completion of current operation.
SQL Server can lock following resources
* Data or index page
* Table schema
Delete statement acquires row lock and hence rows for delete operation is locked from other operation
Truncate statement acquires locks on tables and data pages not each row.
Fewer locks in truncate operate reduces over head of deletion of records with minimum transaction log. Because of that Truncate is much faster than Delete Statement
As we allready discussed truncate won’t write log for each removed record like delete.So
Truncate operation can’t activate any delete trigger on table.
Foreign Key Restriction
if your table column referenced by foreign key then you can’t truncate the table even if the referred table is empty.
If you wan’t to truncate such tables you need drop the constraint then recreate the constraint after truncate operation.
But in case of delete operation you can delete rows,if there is no reference to the row.