Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

MSSQLTips author Edgewood Solutions By:   |   Read Comments (11)   |   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:

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 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")

DBCC CHECKIDENT with RESEED

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
MSSQLTips author Edgewood Solutions
Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server 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.


Wednesday, June 26, 2013 - 2:33:35 PM - Rob Read The Tip

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, July 24, 2013 - 8:30:55 AM - Marcel Otten Read The Tip

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.

 



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.