Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Deleting Data

In this section, I’ll cover different aspects of deleting data, including TRUNCATE vs. DELETE, removing rows with duplicate data, DELETE using joins, large DELETEs, and DELETE with OUTPUT.

TRUNCATE vs. DELETE

If you need to remove all rows from a table, use TRUNCATE TABLE and not DELETE without a WHERE clause. DELETE is always fully logged, and with large tables it can take a while to complete. TRUNCATE TABLE is always minimally logged, regardless of the recovery model of the database, and therefore, it is always significantly faster than a DELETE. Note though, that TRUNCATE TABLE will not fire any DELETE triggers on the table. To give you a sense of the difference, using TRUNCATE TABLE to clear a table with millions of rows can take ...