Geeks With Blogs
Chris Breisch   .NET Data Practices
Search this Blog!

Dennis Gobo explores the misunderstood SQL TRUNCATE TABLE statement. He starts by first exploding the myth that TRUNCATE TABLE is not logged.  He even uses the Books On-Line (amazing idea, reading the documentation) to back up his assertion.  From Books On-Line (emphasis mine):

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

He then gives an example of a TRUNCATE with a ROLLBACK to prove his point.  He does point out some differences between TRUNCATE TABLE and DELETE related to IDENTITY columns and FOREIGN KEY constraints.  Read the whole thing.

Posted on Friday, July 6, 2007 2:25 PM Database Practices | Back to top

Comments on this post: SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Chris J. Breisch | Powered by: