The Difference Between Truncation and Deletion

From WikiContent

Revision as of 18:13, 21 January 2010 by Chapmandew (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

The Difference Between Truncation and Deletion

The removal of data is a common activity inside all database systems. There are two mechanisms to remove records inside the database engine; the DELETE statement and the TRUNCATE TABLE statement. While both of these statements are used to remove information, there are distinct differences between them.

DELETE The DELETE statement removes records from a database table at a row-level. When a record is removed using DELETE, records are committed to the transaction log for the opration, and any triggers are fired. Records that have been accidentally removed using DELETE can be recovered with some added effort. DELETE can be used to remove all of the records from a table or, more commonly, 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 to more related tables.

DELETE permissions on tables can be specified to specific users. DELETE operations do not reseed identity columns.

TRUNCATE The TRUNCATE statement deallocates all data pages from a table, rendering the entire table completely empty after execution. This operation signficantly faster than the DELETE statement due to the page deallocation and that very minimal information is tracked in the transaction log for a TRUNCATE statement. Because it is very minimally logged, once the TRUNCATE statement has committed it cannot be undone.

If the truncated table has IDENTITY columns, they will be reseeded according to their original definition.

TRUNCATE is a data-definition language (DDL) operation, which means that ALTER TABLE or higher permissions are necessary to execute it.

TRUNCATE TABLE 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 replicated. -when the table belongs to a database that is being log-shipped.

which also decreases the resources necessary to perform the operation.

When to use DELETE vs TRUNCATE depends entirely on your situation. DELETE is by far the most commonly used method to remove data. DELETE allows you to specify the records you want to delete using JOINs and other necessary criteria. 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 effeciently.

Personal tools