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.

Hallerson

I should have clarified that I am using MSSQL 2005 server. – Hallerson

This lists all the commands that can NOT be used in a SQL Server transaction. TRUNCATE is not listed, so it is allowed. – Amarelle

Answer 1

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

Plainspoken

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

Thermopile

This is still true for Oracle 21c – Supererogate

Answer 3

If you read the official documentation of PostgreSQL, It said that The TRUNCATE TABLE statement is transaction-safe.

Fusionism

The author is referring to MSSQL, not Postgre. This specific topic could differ between the two. – Crier