Hi, Walter,

Both you and Bill F. essentially gave the same answer. Many others with large amounts of SQL experience reply in similar fashion. And the very first time I had the question posed to me, I gave the same answer....that no, you couldn't issue a ROLLBACK and get the rows back, because the TRUNCATE does not log.

Turns out, we're all wrong. :)

As it turns out, at least going back to SQL 2005 (and maybe even 2000, though I haven't confirmed it), you CAN issue a ROLLBACK and get the rows back.

Of course, the reply back is often, "but how is that possible? If I issue a TRUNCATE on a table with a billion rows, it occurs almost instantly - so how can it be logging the deletion of the rows, when a DELETE of all rows would certainly take seconds or even minutes?"

The answer is "what" specifically SQL Server logs during the truncate - it logs the de-allocation of the data file pages associated with the table. If you issue a rollback, SQL Server will effectively "allocate back" the data file pages, if you're in the middle of a transaction.

A bit of an oversimplification, but in this context, the transaction manager is acting not unlike a recycling bin.

I learned this back in late 2008. Ironically, just a short time later I applied for a SQL contract and had to take a test - and this question was on the test. I answered it correctly, but was told later it was a question I missed. I probably went further than I needed to contact the original author of the test, to actually demonstrate this.

Granted, this is definitely "esoteric" - but still interesting.

Thanks for the responses...