Minimally logged/Non-logged operation and truncate table

Nonlogged operations offer much better performance than logged operations.

The following DML operations are either nonlogged or minimally logged:

TRUNCATE TABLE.

SELECT INTO.


The TRUNCATE TABLE statement cannot be used on tables that are referenced by a FOREIGN KEY constraint, unless the FOREIGN KEY constraint is self-referencing. The TRUNCATE TABLE statement cannot be used on a table that is a part of an indexed view. You cannot use the TRUNCATE TABLE statement on tables that are published by transactional or merge replication. The TRUNCATE TABLE statement will not activate triggers, as triggers rely on transaction logs

The TRUNCATE TABLE statement uses much less transaction log space. The DELETE statement logs information about every row that is affected in the transaction log. When deleting from a table with millions of rows, this is both time- and disk-space-consuming.

The DELETE statement holds a lock on each individual row it is deleting. The TRUNCATE TABLE statement locks only the table and each data page. This offers better performance, as locking is one of the most time-consuming SQL Server activities.

The DELETE statement will leave empty data pages for the table and its indexes. If you wish to shrink the database by deleting data in a large table, the DELETE statement may prove counterproductive. The TRUNCATE TABLE statement, on the other hand, is guaranteed to leave zero data pages behind.