Truncate a table in SQL

So one of the things I am working on is creating a shell database of an existing LOB database we have in production.  To accomplish this I need to erase data from several tables in the database and preferably do it in a very quick manner so I am thankful for the TRUNCATE statement.  This allows for really fast deletions of data in SQL.  One caveat I’d like to point out is that you can not use TRUNCATE with any conditions.  You either want all the data in the table gone or you don’t.


In the first screen capture we see there is data in the table I am going to truncate.


I then use the following code to erase the data.




As you can see the data is now gone.  Granted this table only had 11 records TRUNCATE is the quicker alternative to DELETE.  If you use DELETE SQL has to manually delete every row in the table row by row.  If you a couple hundred thousand records you can see how this would be a very painful process.

If you want the full details of the TRUNCATE statement check out this article from MSDN.

1 thought on “Truncate a table in SQL

  1. Nathaniel Reply

    An outstanding share! I’ve just forwarded this onto a friend who was conducting a little research
    on this. And he actually bought me breakfast simply because
    I stumbled upon it for him… lol. So let me reword this….
    Thanks for the meal!! But yeah, thanx for spending some time
    to discuss this topic here on your web page.

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.