Difference Between TRUNCATE TABLE and DELETE FROM Table

Download PDF

Most DBAs know the usual difference between TRUNCATE and DELETE FROM, however one not so obvious difference between the two is how things are handled if the table is corrupt.

For instance, if you look at the corrupt database from the Database Corruption Challenge Week 1, you see the following

DeleteVsTruncate1

If you want to clear out the corrupt Revenue table, one way to attempt to do it is with a DELETE statement like this.


DELETE FROM dbo.Revenue;

DELETE FROM vs TRUNCATE TABLE

Not only does SQL Server say NO, but it drops your connection as well. Reconnecting and querying the table shows that nothing changed with the delete statement.

TRUNCATE TABLE

Next lets take a look at TRUNCATE.


TRUNCATE TABLE dbo.Revenue;

TRUNCATE TABLE

The TRUNCATE statement works and does just what you would expect. Querying the Revenue table shows that the table is indeed empty, and if you run DBCC CheckDB after the corrupt table has been truncated you will see that the corruption is also gone for this database.

 

Just something interesting to consider when you are dealing with corruption, or trying to clear out a table.

Related Links

 

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

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

*