Tuesday, April 1, 2008

FAQ : What is the difference between DELETE TABLE and TRUNCATE TABLE commands

TRUNCATE

• Less Transaction Log entry because TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log and hence TRUNCATE is fast
• TRUNCATE apply Fewer table locks
• TRUNCATE is DDL Statement
• TRUNCATE Release the tables spaces to System
• TRUNCATE Can not have WHERE Conditions
• TRUNCATE does not fire trigger
• TRUNCATE reset the identity to 0 (if table have any)
• TRUNCATE Can not be used against the tables involved in TRUNCATE transactional replication or merge replication.
• TRUNCATE Can not be used against the table used in Indexed view
• TRUNCATE can not be used against the table that Are referenced by a FOREIGN KEY constraint.
• TRUNCATE commands are not tracked by DDL trigger

Note : TRUNCATE can be rollbacked. I have seen many place where its is mentioned that it can not

DELETE

• DELETE FROM TABLE command logs each records in transaction log , hence DELETE is slow.
• DELETE apply more locks to the table
• DELETE is a DML command
• DELETE remove the records but will not release the space to the system
• DELETE can have WHERE conditions
• DELETE Fires TRIGGER
• DELETE do not RESET IDENTITY
• DELETE Can be used against table used transactional replication or merge replication
• DELETE Can be used in tables reference by a Foregin Key and tables involved in Indexed view

1 comment:

Dan Pretselman said...

Also you cannot use(directly) truncate command over Linked Server,because it DDL command.

Dan
http://MadeBySQL.blogspot.com/

 
Locations of visitors to this page