Truncate vs Delete

There are two keywords used for deleting data from a table: TRUNCATE and DELETE. While the end result is the same, the way SQL Server executes the statements are greatly different. As always there are advantages, disadvantages, limitations, and of course consequences. You should consider all when determining the best method to use for the situation at hand.

Deleting Data Using TRUNCATE TABLE: TRUNCATE TABLE is a statement that deletes all records in a table by de-allocating the data pages used by the table. This will reduce the resource overhead of logging the deletions, and the number of locks needed; Note: This will not log transactions in the transaction log. The only record a truncation every took place is a logging of a page de-allocation. The records removed by the TRUNCATE statement cannot be restored. You also can not specify a WHERE clause in a TRUNCATE statement. The main advantage to using TRUNCATE is in addition to removing all rows from the table it will reset the IDENTITY back to the SEED, and the de-allocated pages are released back to the system for use again in other areas.

TRUNCATE table “table_name”

The TRUNCATE statement can not be used on any tables with replication or log shipping set up, because these both depend on the transaction log to keep remote databases in sync.

TRUNCATE can not used be used when a foreign key reference is enabled on  the table, since the TRUNCATE statement will not fire triggers. This has the potential result of causing inconsistent data because ON DELETE/UPDATE triggers would not fired. If you need all the table rows deleted and you have a foreign key reference on the table, you will need to drop the index and recreate it. Attempting to execute a TRUNCATE statement on a table with a foreign key reference, will result in the following error;

“Cannot truncate table ‘table_name’ because it is being referenced by a FOREIGN KEY constraint”.

Deleting Data Using DELETE FROM Statement

DELETE TABLE statement will delete rows one at a time, and will log each row in the transaction log, and maintain log sequence number (LSN) information. This consumes will result in resources and locks. This will allow transactions to be rolled back if necessary. You may also use a WHERE clause to narrow down the rows you wish delete. When deleting a large number of rows with the DELETE FROM statement, there is the potential for SQL Server to not release the empty pages requiring manual release. The following statement can be used in this situation.

DBCC SHRINKDATABASE (db_name)

When large tables require that you to delete all records and TRUNCATE statement can not be used, using the following statement will give you the same result.

DELETE FROM “table_name”
GO
DBCC CHECKIDENT(“table_name”, RESEED, “reseed_value”)
GO

Links to MSDN official definitions.

truncate = http://msdn.microsoft.com/en-us/library/aa260621.aspx
delete = http://msdn.microsoft.com/en-us/library/aa258847.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s