Truncate a table without removing all constraints

Posted on: 2014-05-01

Truncated a table as the advantage to remove every data without logging or locking every rows. This is a huge advantage over the Delete statement. This mean that truncated table cannot be restored, neither you can have a where clause into the SQL statement. Truncate also has the ability to reset the seed to its initial value. On delete triggers are also not fired and all foreign keys constraint must be removed or disabled.

Instead of removing all constraints, it is possible to tell SQL Server to not check foreign key.

 SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE [Schema].[Table]; SET FOREIGN_KEY_CHECKS = 1; 

This will result to remove data of the table in the fastest way possible. If you want to have more details you can check that article that I previously wrote about Truncate vs Delete.