I recently ran a poll on my Twitter and LinkedIn profiles asking if you could rollback a truncate in SQL Server?
I kind of wish I didn’t include the ‘It depends’ option for the LinkedIn poll as I suspect a lot of people might have picked that as it looks right as it usually does ‘depend’ but here are the results…
We can see the majority of people think you cannot rollback a truncate in SQL Server…
… BUT the reality is you CAN ROLLBACK A TRUNCATE in SQL Server
SQL Server Truncate is a logged command which logs page de-allocations rather than individual records being deleted but it can be rolled-back once inside a transaction.
For example the ‘ROLLBACK TRAN’ in the below sample restores the records in the ‘TruncateTest’ table…
The result of the polls above were interesting but not surprising to me as it’s a common belief that a SQL Server truncate cannot be rolled-back.
Even ChatGPT gets this one wrong…
Truncate v delete is a great interview question but mentioning that truncate can in fact be rolled back in SQL Server is always nice to get extra brownie points 💪 as most people think it’s not possible. Does anyone know where this idea came from?
