The Difference Between Truncation and Deletion

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Line 22: Line 22:
-when the table belongs to a database that is being log-shipped.
-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.
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.

Revision as of 18:11, 22 January 2010

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 simliar in that they are used to remove data, 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, 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. 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 to 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 signficantly 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 IDENTITY 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 replicated.

-when the table belongs to a database that is being log-shipped.


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