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.

In the May 17, 2001 SQL Server TechMail, we stated that the truncate table function could not be performed within a transaction. While this was true in early versions of SQL Server, version 7.0 will support truncate table within a transaction.