Truncate Table Within Transaction

Question

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.

Solution 1

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

Solution 2

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.

Comments

[dr fu manchu]

This is still true for Oracle 21c