Table Deletions

The following example deletes the contents of large_tab within a single transaction and logs the complete before-image of every row in the transaction log:

delete large_tab

If this transaction fails before completing, SQL Server can roll back the transaction and leave the table as it was before the DELETE. Usually, however, you do not need to provide for the recovery of a DELETE operation, so the logging done by an unqualified DELETE statement might not always be needed. If the operation fails halfway through, you can simply repeat it and the results will be the same.

To accomplish the same results as the DELETE statement without the extensive logging, you can use the TRUNCATE TABLE statement instead. For example:

truncate table large_tab

This statement also deletes the contents of the table, but it logs only space deallocation operations, not the complete before-image of every row.