Menu
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.
876
In Oracle, TRUNCATE TABLE is a DDL statement that cannot be used in a transaction (or, more accurately, cannot be rolled back).
Truncate cannot be used in certain cases like replication / foreign key contraints, etc. As per Microsoft documentation you need to use delete. You cannot use TRUNCATE TABLE on tables that: Are referenced by a FOREIGN KEY constraint.
When you execute a Truncate statement, it does not get logged in the log file as it is a DDL statement. So if you Truncate a table, you cannot Roll Back to a point in time before the truncate. However, in a Transaction, Rollback is permitted and functions just as any other rollback would.
Yes, it is possible to use many ALTER TABLE statements in one transaction with ROLLBACK and COMMIT .
In SQL Server, you can rollback a TRUNCATE from a transaction. It does write page deallocation to the log, as you mentioned.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
