Deleting Data in SQL Server with TRUNCATE vs DELETE commands

By:   |   Updated: 2006-10-11   |   Comments (15)   |   Related: 1 | 2 | > TSQL


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 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 foreign key and then recreate it. If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Error message when attempting TRUNCATE TABLE on table referenced by FK

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 tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following statements can be used to delete the data and reset the identity value:

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author MSSQL Tips MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-10-11

Comments For This Article




Wednesday, January 25, 2017 - 9:21:25 AM - David Berg Back To Top (45569)

Note that it's possible to truncate part of a table by using partitions.  For example, data could be partitioned by day or month, and then older partitions truncated when they are no longer needed.

Friday, November 6, 2015 - 12:23:58 PM - Bob Zwolinski Back To Top (39035)

Thank you for posting this! This is precisely what I needed!!!

Wednesday, July 29, 2015 - 7:14:48 AM - Greg Robidoux Back To Top (38310)

@jitendra

You can use the TRUNCATE TABLE command to delete all of the data.

TRUNCATE TABLE tableName

Wednesday, July 29, 2015 - 3:28:01 AM - jitendra Back To Top (38307)

I want to delete all the data in table from database. please suggest me single query for that

Wednesday, July 24, 2013 - 8:30:55 AM - Marcel Otten Back To Top (25977)

Simon,

have you considered changing the recovery model of your test-database to 'simple' when you prune it?

Keep the log small by taking CHECKPOINTs after each delete.

When done pruning you can switch back to the recovery model that was used before.

 

Wednesday, June 26, 2013 - 2:33:35 PM - Rob Back To Top (25579)

I have noticed that if i do a delete from table, alot of times i get this error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 

but if i change the sql job to a Truncate then i don't get this error.

 

and my network is fine outside of this very brief moment in time.  but in this case, i can't do a truncate because i have to have a WHERE clause.  has anyone else experienced this? any solutions please? thanks.

Wednesday, May 22, 2013 - 2:11:15 PM - PM Back To Top (24084)

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.

Thursday, May 16, 2013 - 12:30:58 PM - Simon Holzman Back To Top (23992)

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 ?

Sunday, January 13, 2013 - 3:32:53 PM - Jeremy Kadlec Back To Top (21427)

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

Sunday, January 13, 2013 - 1:44:23 PM - srikanth Back To Top (21426)

u cant give any condition in truncate..................

Thursday, December 13, 2012 - 10:26:52 AM - RD Francis Back To Top (20935)

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

Saturday, September 29, 2012 - 2:36:59 PM - Joe Back To Top (19746)

This is not 100% correct. TRUNCATE can be used on a table in the database configured for log shipping.

Wednesday, September 12, 2012 - 4:22:48 AM - Maqsood Back To Top (19479)

I would like know that how to delete the table in the sql server 2008 so please kindly provide me the exact query same.

Wednesday, March 21, 2012 - 1:28:07 AM - Santosh S.Pawar Back To Top (16551)

Hi sandeep,

you are correct.. it is possible to rollback truncated data by usin transaction.

Sunday, August 8, 2010 - 3:30:12 AM - sandeep Back To Top (10024)
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














get free sql tips
agree to terms