Difference Between TRUNCATE TABLE and DELETE FROM Table

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

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;

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;

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

• Stedman Solutions, LLC for SQL Server help, including fixing corruption.
• Database Corruption

More from Stedman Solutions:

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!