Deleting Data in SQL Server with TRUNCATE vs DELETE commands

By: Edgewood Solutions   |   Read Comments (9)   |   Related Tips: More > T-SQL

Problem
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

Solution
Deleting Data Using TRUNCATE TABLE

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.
TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the index and recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Deleting Data Using DELETE FROM Statement

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

Next Steps

• Review information on the TRUNCATE TABLE statement and the DELETE statement.
• Review the structure of tables you need to rid of large amounts of data from
• Stay tuned to MSSQLTIPS for more information

Last Update: 10/11/2006

About the author

Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Sunday, August 08, 2010 - 3:30:12 AM - sandeep	Read The Tip
Hello ,              You have mentioned a point that truncated rows cannot be restored. But this is worng .. u can always do it using transactions.   Sample :-   begin transaction aa truncate table testsort select * from testSort rollback transaction aa select * from testSort

Wednesday, March 21, 2012 - 1:28:07 AM - Santosh S.Pawar	Read The Tip
Hi sandeep, you are correct.. it is possible to rollback truncated data by usin transaction.

Wednesday, September 12, 2012 - 4:22:48 AM - Maqsood	Read The Tip
I would like know that how to delete the table in the sql server 2008 so please kindly provide me the exact query same.

Saturday, September 29, 2012 - 2:36:59 PM - Joe	Read The Tip
This is not 100% correct. TRUNCATE can be used on a table in the database configured for log shipping.

Thursday, December 13, 2012 - 10:26:52 AM - RD Francis

Read The Tip

You neglect to mention the most obvioius difference between the two.  TRUNCATE TABLE is basically instantaneous, regardless of the number of records in the table.  Using DELETE on a table with tens of millions of records can take hours, meaning that you may be unable to complete the deletion in an overnight maintenance window. This can, in fact, be so significant tha, if you are deleting more than two-thirds of a very large table, it may be more practical to write the records you want to keep to new table, TRUNCATE the original table, and the write the records you saved back into the original table.  [More than two-thirds is an estiamte as to where the tipping point would be - YMMV].

Sunday, January 13, 2013 - 1:44:23 PM - srikanth	Read The Tip
u cant give any condition in truncate..................

Sunday, January 13, 2013 - 3:32:53 PM - Jeremy Kadlec	Read The Tip
srikanth, No - There is no WHERE condition for the TRUNCATE TABLE command.  This command is applied to the entire table. Thank you, Jeremy Kadlec Community Co-Leader

Thursday, May 16, 2013 - 12:30:58 PM - Simon Holzman

Read The Tip

I have an unusual situation... For testing purposes, I copy production to a test database and then need to prune the test database of the majority of the data. Since I cannot use a WHERE clause on a Truncate, I have been using DELETE statements, but these take a long time and fail if I try to do too much at once since the log files get too large. I would like to copy the data I need to preserve to a backup table, truncate the original table and then load the preserved data from the backup table into the original one. Two problems; 1. The tables all have timestamp type fields which seem to require that the fields be specified when backing up the table. There are a LOT of tables potentially, so I am loath to do this, though it is certainly possible to write a script to generate the sql code to do it dynamically and then just execute the scripts. 2. Many of the tables have constraints, foreign keys, triggers etc on them and I naturally need to preserve these, or at least save them and recreate them once the update is complete. Any ideas ?

Wednesday, May 22, 2013 - 2:11:15 PM - PM	Read The Tip
Simon, it sounds as though you need to write a bunch of T-SQL.    You also may want to check out SSIS - you can make a job that does the work for you, schedule it to run, or manually kick it off.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think