 |
|
Deleting Data in SQL Server with TRUNCATE vs DELETE commands
|
By: Edgewood Solutions | Read Comments (2) | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: More |
|
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.
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
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
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. |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |