>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...

So it works like "mark records for deletion" and defers the "pack" until the commit?