SQL Server: DELETE vs TRUNCATE

T-SQL provides two keywords that allow you to delete data from a table. They are DELETE and TRUNCATE. These are alike in what they are doing, however, they  use vastly different methods. In this article, we discuss underlying mechanisms for these keywords.

A difference between TRUNCATE and DELETE

TRUNCATE:

1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page. However, it doesn’t lock each row.
3. The WHERE condition can not be used.
4. The command removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance, because it does not keep logs.
7. Rollback is possible.

DELETE:

1. DELETE is a DML Command.
2. DELETE statement uses a row lock while executing. Each row in the table is locked for deletion.
3. You can specify filters in WHERE clause.
4. The command deletes specified data if the WHERE condition exists.
5. DELETE activates a trigger because the operations are logged individually.
6. DELETE is slower than TRUNCATE because it keeps logs.
7. Rollback is possible.

How to delete data with the help of TRUNCATE?

The following example removes all data from the Person table. SELECT statements are included before and after the TRUNCATE TABLE statement to compare results.

USE AdventureWorks2012;
GO
SELECT COUNT(*) AS BeforeTruncateCount
FROM Person.Person;
GO
TRUNCATE TABLE Person.Person;
GO
SELECT COUNT(*) AS AfterTruncateCount
FROM Person.Person;
GO

Let’s highlight some advantages.

The TRUNCATE TABLE command deletes all rows from a table by deallocating the data pages used to store the table data. Once the operation is completed, the table contains zero pages. It does no logging for the individual row deletions. The command records page deallocation to the transaction log. The TRUNCATE command uses less log space. Fewer locks are typically used. The command deletes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table. you need to use DROP TABLE.

If a table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Deleting all rows by using DELETE

The following example deletes all rows from the Person table:

DELETE FROM Person.Person;
GO

DELETE removes rows one at a time. The command adds the new record to the transaction log for each deleted row. DELETE uses a row lock while executing, which means each row in the table is locked for deletion. Once DELETE is executed, a table can still contain empty data pages. For instance, empty pages in the heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

Read also

Difference Between DELETE and TRUNCATE Table in SQL Server