Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

The Difference Between Truncation and Deletion in Microsoft SQL Server

chapmandew
CERTIFIED EXPERT
Published:
The removal of data is a common activity inside all database systems. There are two mechanisms to remove records inside the SQL Server database engine: the DELETE statement and the TRUNCATE TABLE statement. While both of these statements are similar in that they are used to remove data, there are distinct differences between the two.

DELETE

The DELETE statement removes records from a database table at a row-level. When a record is removed using DELETE, entries are recorded in the transaction log for the operation, constraints are checked, and any triggers are fired. Records that have been accidentally removed using DELETE are typically able to be recovered with an added amount of effort provided a database recovery model is used to make such actions available. While DELETE can be used to remove all of the records from a table it is much more common to remove a smaller subset of records that match specific criteria. DELETE may be used in single-table statements, or as a method to remove data from a single table when joining to one or more related tables. DELETE permissions on tables can be specified to specific users without the need to assign object ownership to the user. A DELETE statement does not modify the seeding of auto-incrementing columns if the table contains them.

TRUNCATE

The TRUNCATE statement deallocates all data pages from a table, rendering the entire table completely empty after execution. This operation is significantly faster than the DELETE statement due to the page deallocation and that very minimal amount of information that is tracked in the transaction log. Because it is very minimally logged, once the TRUNCATE statement has committed it cannot be undone. TRUNCATE is a data-definition language (DDL) operation, which means that ALTER TABLE or higher permissions are necessary to execute it. A 'TRUNCATE TABLE' permission does not exist. If the truncated table has auto-incrementing columns, they will be reseeded according to their original definition. There are also additional restrictions on a TRUNCATE statement. It may not be issued against tables in the the following situations:

when the table is referenced by an index view.
when foreign keys reference the table to be truncated.
when the table is published for replication.
when the table belongs to a database that is being log-shipped.

WHEN TO USE

Your specific situation should dictate whether to use DELETE or TRUNCATE to remove data. DELETE is by far the most commonly used method to remove data as it allows you to specify the records you want to remove using JOINs and other necessary criteria. Blocking may occur when removing large amounts of data using DELETE, so care must be taken when designing such statements. Permissions for DELETE operations are easily assigned to users or roles without the ownership necessary for the underlying tables. TRUNCATE is more of an administrative function to be used in those situations where all data needs to be removed from a table quickly and efficiently.
17
9,302 Views
chapmandew
CERTIFIED EXPERT

Comments (7)

Commented:
I was just browsing the SQL articles and came across this.  I thought I'd give it my YES as I've seen this question in at least 3 interviews now and have had to know it in prior situations myself; everyone who doesn't know what truncate means should read this!

Commented:
"once the TRUNCATE statement has committed it cannot be undone."

Not sure what is meant by this statement. The same can be said of both DELETE and TRUNCATE: a point-in-time restore is the only way to undo the operation once committed.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Author

Commented:
I was really talking in terms of recovering it from a 3rd party tool (w/o having to recovery to a point in time).  But, as it so happens, I wasn't 100% correct.  It seems that ApexSQL can do some tricky things now w/ their Log product, and it is able to recover a truncated table (through some sort of magic). :)

http://knowledgebase.apexsql.com/2009/08/recover-from-rogue-truncate-table.htm
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Excellent illustration of TRUNCATE.  Voted Yes.

Commented:
SQL Server's TRUNCATE is a strange hybrid between DML and DDL because you can roll it back before a COMMIT within a transaction.  On most other DBMS it is strictly DDL because it cannot be trapped within a transaction.  Most people keep auto-commit enabled so it appears to be DDL in SQL Server, but it isn't. Try:

BEGIN TRANSACTION TRUNCTEST;
TRUNCATE TABLE mytable;(some table that has some rows)
SELECT * FROM mytable;
ROLLBACK TRANSACTION TRUNCTEST;
SELECT * FROM mytable;

On Oracle, for comparison, the table would still be empty after the rollback because TRUNCATE there is immediate and unrecoverable.

SQL Server's own documentation refer to TRUNCATE as a "minimally logged" operation and avoids identifying it either way as DDL or DML.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community