• Sean Lange (5/9/2013)


    The difference between delete and truncate is that delete is a dml operation that is fully logged and truncate is DDL operation and is minimally logged. What that means is the rows are logged in a delete but the page deallocation is logged for a truncate.

    Technically truncate is a fully logged operation. Minimally logged means that there is a difference in logging behaviour between bulk-logged/simple and full recovery and that in bulk-logged and simple recovery models the operation logs only enough for a roll back, not enough for a roll forward operation, such an operation flips the appropriate bit in the ML map and results in the next log backup copying the affected extents into the log backup.

    Minimally logged does not mean that only the page allocation/deallocations are logged. Many operations, including truncate table, drop table, drop index, select into, insert into, alter index .. rebuild, log only page allocations/deallocations in all recovery models.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass