Deleting Data in SQL Server using TRUNCATE vs DELETE commands

Hi,

Today we will discuss various ways to delete rows from table.

There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although both achieves the same result, the method employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

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 unless you use TRANSACTION.

--Create a simple table with 1 identity column
create table ttt
(id int identity)

insert into ttt --Insert 100 rows into table
default values
go 100

select * from ttt --check number of rows from the table

--Now run this in a transaction and roll back and check if you are able to retrieve your data or no.
begin tran
truncate table ttt
rollback

select * from ttt


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:

Cannot truncate table 'TableName'

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

CHECKIDENT checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT("ttt", NORESEED)

This will return the current last value of Identity in table.
DBCC CHECKIDENT("table_name", RESEED, "reseed_value")
This will reset the Identity column value to start from "reseed_value", if you have a unique constraint, then this will fail as it will start from 1, if you seed was 1.

DBCC CHECKIDENT("ttt", RESEED, 0)

Happy SQL Coding..................................................