Truncate Table Within Transaction

SqlSql ServerSql Server-2005TransactionsTruncate

Sql Problem Overview


Can the SQL "truncate table" command be used within a transaction? I am creating an app and my table has a ton of records. I want to delete all the records, but if the app fails I was to rollback my transaction. Deleting each record takes a very long time. I'm wondering if I use truncate table, can I still rollback the transaction and get my data back in the event of a failure. I realize that truncate table doesn't write each delete to the transaction log, but I'm wondering if it writes the page deallocation to the log so that rollback works.

Sql Solutions


Solution 1 - Sql

In SQL Server, you can rollback a TRUNCATE from a transaction. It does write page deallocation to the log, as you mentioned.

Solution 2 - Sql

In Oracle, TRUNCATE TABLE is a DDL statement that cannot be used in a transaction (or, more accurately, cannot be rolled back). AFAIK, if there is a transaction in progress when the statement is executed, the transaction is committed and then the TRUNCATE is executed and cannot be undone.

In Informix, the behaviour of TRUNCATE is slightly different; you can use TRUNCATE in a transaction, but the only statements permissible after that are COMMIT and ROLLBACK.

Other DBMS probably have their own idiosyncratic interpretations of the behaviour of TRUNCATE TABLE.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDavin StuderView Question on Stackoverflow
Solution 1 - SqlwompView Answer on Stackoverflow
Solution 2 - SqlJonathan LefflerView Answer on Stackoverflow