Tuesday, October 19, 2010

Truncate Table

TRUNCATE TABLE

* Difference Between Truncate Table and Delete from

 * You can put a WHERE condition in DELETE FROM but not in TRUNCATE TABLE
 * If the TABLE has a trigger on DELETE, the trigger is NOT fired when TRUNCATE TABLE is called.
 * TRUNCATE TABLE resets the IDENTITY of the table (i.e. the identity restarts from SEED Value).This does
   not happen in DELETE FROM

Not All tables can be truncated
You cannot TRUNCATE a table which are referenced by FOREIGN KEY CONSTRAINT (i.e. SOME OTHER TABLE HAS
A FOREIGN key referencing this table.
You cannot truncate a table which participates in an  indexed view.
You cannot truncate tables that are published using TRANSACTION REPLICATION or MERGE REPLICATION.

http://msdn.microsoft.com/en-us/library/ms177570.aspx

No comments:

Post a Comment