Inside Microsoft® SQL Server® 2008: T-SQL Querying

Deleting Data

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

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 DELETE. Note, though, that TRUNCATE TABLE does 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 a matter of seconds, ...