SQL 2005+Truncating Tables in SQL Server
Deleting large amounts of data from a SQL Server table can be an expensive operation, in terms of transaction log space, memory usage and the creation of locks. When emptying a table completely, truncating the table can be a less expensive option.
Deleting Table Rows
When you wish to remove rows from a SQL Server table, you will usually use the DELETE keyword. You can use a DELETE statement to remove individual rows or groups of rows that match the criteria defined in a WHERE clause, giving fine-grained control over the operation. If you omit the WHERE clause entirely, you can even use the command to remove all of the rows from a table. For example, the following statement removes all of the rows from an "Employees" table.
Deleting all of the rows from a table in this manner can be an expensive process. Internally, the database management system (DBMS) removes each affected row individually, logging every change in the transaction log. The log file can grow considerably for very large tables and the operation can execute slowly as a result. Additionally, the DELETE command causes row locks to be created, which can adversely affect other operations.
TRUNCATE TABLE Command
If you simply want to quickly empty a table you can often make use of the TRUNCATE TABLE command, rather than deleting rows. Instead of deleting the rows individually, the truncation process simply deallocates the data pages for the data in the affected table. The row deletions are not logged, though the deallocations are, leading to less transaction log space usage and, usually, better performance. In addition, truncating a table does not require row locks; only a table lock and locks for the data pages are needed. Truncation also ensures that all data pages are removed from the table, whereas deletion can lead to the retention of empty pages.
To use the TRUNCATE TABLE command, simply add the name of the table to be affected. For example, the following statement removes all data from an "Employees" table.
Limitations and Considerations
As TRUNCATE TABLE works in a fundamentally different manner to DELETE, there are some limitations to its use and some considerations to be made before choosing which command to use to remove all data from a table. The key limitations relate to the types of table that may be truncated. If a table is referenced by a foreign key, is used in an indexed view or is published via transactional or merge replication, it may not be truncated. Deletions are required in these situations to ensure that the updates are correctly reflected in related tables and to ensure that referential integrity is maintained.
When you truncate a table that contains an identity column, the identity is reset to its seed value. If you wish to remove all of the rows from a table but need the next value for the identity column to continue the previous sequence, you should not truncate the table.
When you delete a number of rows using a DELETE statement, any delete triggers on the table are executed. These may make modifications elsewhere in the database or prevent the data from being removed. When truncating a table, the triggers are not executed so if you need triggers to behave normally, truncating is not suitable.
Truncating a table requires different permissions to deleting rows from within it. Specifically, users must have the permission to alter the table. If you do not wish to provide your users with the ALTER permission, you should use DELETE to empty tables.
Rolling Back Truncations
A final note on truncating tables. Some users believe that the TRUNCATE TABLE command is not included in database transactions. This would mean that truncating a table within a transaction was not reversible by a rollback. However, that is not the case. If you were to execute the following script against a test database you should find that the truncation is rolled back and no data is lost.
BEGIN TRAN
TRUNCATE TABLE Employees
ROLLBACK TRAN
29 March 2012