There are two keywords used for deleting data from a table: TRUNCATE and DELETE. While the end result is the same, the way SQL Server executes the statements are greatly different. As always there are advantages, disadvantages, limitations, and of course consequences. You should consider all when determining the best method to use for the situation at hand.
Deleting Data Using TRUNCATE TABLE: TRUNCATE TABLE is a statement that deletes all records in a table by de-allocating the data pages used by the table. This will reduce the resource overhead of logging the deletions, and the number of locks needed; Note: This will not log transactions in the transaction log. The only record a truncation every took place is a logging of a page de-allocation. The records removed by the TRUNCATE statement cannot be restored. You also can not specify a WHERE clause in a TRUNCATE statement. The main advantage to using TRUNCATE is in addition to removing all rows from the table it will reset the IDENTITY back to the SEED, and the de-allocated pages are released back to the system for use again in other areas.
TRUNCATE table “table_name”
The TRUNCATE statement can not be used on any tables with replication or log shipping set up, because these both depend on the transaction log to keep remote databases in sync.
TRUNCATE can not used be used when a foreign key reference is enabled on the table, since the TRUNCATE statement will not fire triggers. This has the potential result of causing inconsistent data because ON DELETE/UPDATE triggers would not fired. If you need all the table rows deleted and you have a foreign key reference on the table, you will need to drop the index and recreate it. Attempting to execute a TRUNCATE statement on a table with a foreign key reference, will result in the following error;
“Cannot truncate table ‘table_name’ because it is being referenced by a FOREIGN KEY constraint”.
Deleting Data Using DELETE FROM Statement
DELETE TABLE statement will delete rows one at a time, and will log each row in the transaction log, and maintain log sequence number (LSN) information. This consumes will result in resources and locks. This will allow transactions to be rolled back if necessary. You may also use a WHERE clause to narrow down the rows you wish delete. When deleting a large number of rows with the DELETE FROM statement, there is the potential for SQL Server to not release the empty pages requiring manual release. The following statement can be used in this situation.
DBCC SHRINKDATABASE (db_name)
When large tables require that you to delete all records and TRUNCATE statement can not be used, using the following statement will give you the same result.
DELETE FROM “table_name”
DBCC CHECKIDENT(“table_name”, RESEED, “reseed_value”)
Links to MSDN official definitions.