O'Reilly logo

Inside Microsoft® SQL Server™ 2005: T-SQL Querying by Dejan Sarka, Lubor Kollar, Itzik Ben-Gan

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

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