O'Reilly logo

Inside Microsoft® SQL Server® 2008: T-SQL Querying by Lubor Kollar Itzik Ben-Gan Dejan Sarka, and Steve Kass

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required