Trim table inside transaction

Question

Can the SQL "crop table" command be used in a transaction? I am creating an application and my table has a ton of records. I want to delete all entries, but if the application failed, I had to cancel my transaction. Removing each entry takes a very long time. I am wondering if I am using the truncate table, can I cancel the transaction and return my data in case of failure. I understand that the truncate table does not write every delete to the transaction log, but I am wondering if it writes a log entry so that the rollback works.

Answer 1

In SQL Server, you can undo TRUNCATE from a transaction. As you mentioned, it writes page deletion to the log.

Answer 2

In Oracle, TRUNCATE TABLE is a DDL statement that cannot be used in a transaction (or, more precisely, cannot be rolled back). AFAIK, if a transaction is executed during a transaction, the transaction ends, and then TRUNCATE is executed and it cannot be undone.

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

Other DBMSs probably have their own idiosyncratic interpretations of TRUNCATE TABLE behavior.