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.

SHOW ME!

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

image

I then use the following code to erase the data.

TRUNCATE TABLE dbo.ACCIDENT

image

 

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.

jcox@hexoma.com
One Comment

Add a Comment

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