Today's SSC QOTD (SQL Server Central Question of the Day) was about Ghost records. A deleted record is not necessarily deleted (reminding me of FoxPro by the way). So, how does the SQL engine mark records for deletion?
It isn't any Black Magic... the Row Header on the data page has a bit to indicate that the row is **marked** for deletion. A background process cleans up these deleted rows as resources permit. If a rollback happens the row isn't "restored", the bit is just flipped again and - voila - the row is undeleted .
Looks like you are venturing into Paul Randal territory. He has a couple of very good posts: - [Fragmentation (part 1): What are records?] where all of them are explained, not just the ghost records - [Inside the Storage Engine: Ghost cleanup in depth], this one is really good. <\!-- **Begin Edit** What do you know, I just went through the posts to today's question on SSC site and it looks like next Tuesday Gail Shaw is going to blog on the subject of how to see deleted data after the transaction to delete it has been committed and the ghost cleanup process has already removed the slots info from the array afterwards. I am sure this is going to be a fascinating read, and I will definitely add it to the collection of life (or at least a job) saving posts. **End Edit** --> :