TRUNCATE TABLE VS. DELETE v7.0

There are two ways to eliminate data from your database table via T-

SQL. The effects are the same, but SQL Server handles them very

differently.

DELETE is probably the most common method for eliminating data, but

TRUNCATE TABLE is faster. The differences are outlined below.

The DELETE clause is a fully logged operation, meaning that if the

DELETE statement is executed within a transaction, then the rows being

deleted can either be committed or rolled back.

BEGIN TRAN

DELETE FROM TableX

COMMIT or ROLLBACK

The TRUNCATE TABLE statement is different from DELETE in that it is not

logged the same way as delete.

TRUNCATE TABLE TableX

Knowing that data is stored in linked page chains, the TRUNCATE TABLE

statement deallocates both the index pages and the data that is

referenced by a table. However, the truncate statement only logs that

the deallocation of pages has taken place. Since the page deallocation

is logged but each row is not, the transaction is processed much faster,

and your log file is burdened with much less data.

TRUNCATE TABLE cannot be used within a transaction or against a table

that has a foreign key constraint referencing it. Such data can be

removed solely via the DELETE statement.

The next time you have to delete data from a large table, consider

using the TRUNCATE TABLE statement if your table meets the requirements

above. You will save both time and log space.