The Difference Between Truncation and Deletion in Microsoft SQL Server

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 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.