Saturday, January 16, 2010

Diff between Delete and Truncate statement

Truncate like delete statement but without WHERE clause with these differences:
1- DELETE statement logs information on each row deleted, while the TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk.
2- Because of the minimal logging, along with how the Database Engine removes the data from the table, the TRUNCATE TABLE statement executes more quickly and requires fewer resources on the server.
3- If an identity column exists in the table, the TRUNCATE TABLE command resets the identity seed value.
4- TRUNCATE will not work on tables, which are referenced by one or more FOREIGN KEY constraints.

No comments: