Delete Vs Truncate in SQL Server

Delete Vs Truncate in SQL Server

Introduction

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

DeleteTruncate
Command TypeDMLDDL
Where Conditionsupportdoes not support
Reset Identity Columnnoyes
Acquired lockrow locktable and page lock
Transaction logfor each deleted rowone log indicating deallocation of page
Performance(Speed of execution) slowmuch faster than Delete

Command Type

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
* Rows
* Data or index page
* Table schema
* Tables
* Database

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.

Post A Reply