Home » Sql » Truncate a table without removing all constraints

Truncate a table without removing all constraints

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.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

3 Responses so far.

  1. You really make it seem so easy with your presentation but I find this topic to
    be actually something that I think I would never understand.
    It seems too complex and very broad for me.
    I’m looking forward for your next post, I’ll try to get the hang of it!

  2. J’ai comme l’іmpression qսe je vais en parler sur mon site
    web

  3. Your troubleshooting can work only if the website of Hotmail is up
    and if this is down, Outlook will certainly not be able to download your messages.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.