Delete vs. Truncate

Delete and Truncate can both be used to remove data from a database but they are far from identical. They both have some differences that should be considered when being used. I’ve specifically seen confusion before about how each affects IDENTITY columns and how each is handled by the transaction log so let’s take a to look into that today.

Delete

What is Delete? Delete is a DML (Data Manipulation Language) statement. You can delete data for an entire table:

DELETE FROM TestTable;
GO

Or you can specify records to delete based on a WHERE clause:

DELETE FROM TestTable
WHERE ID = 1;
GO

Since each row is locked, removed one at a time, and logged in the transaction log, this can take some time based on how much data you are removing. If you’re going to be getting rid of all data in a table, you’ll likely want to look at Truncate or batching those record deletions.

Truncate

Truncate is a DDL (Data Definition Language) statement. This can be used to remove data from an entire table at once:

TRUNCATE TABLE TestTable;
GO

You cannot specify a WHERE clause when using Truncate.

When Truncate is used, fewer locks are used (the table is locked compared to rows being locked with DELETE) and data pages are deallocated. These data page deallocations are then logged to the transaction log.

IDENTITY and Delete/Truncate

Let’s create a test table with an IDENTITY column and add some test records:

CREATE TABLE TestTable 
(
	Id INT IDENTITY(1, 1),
	LName VARCHAR(50)
);

INSERT INTO TestTable
VALUES ('Callihan'),('Carter'),('Lane'),('Doe'),('Lewis');
GO

If we check our table, our IDENTITY column is increasing by one as expected.

If we delete one of these records and add a new record:

DELETE FROM TestTable
WHERE Id = 5;
GO

INSERT INTO TestTable
VALUES ('Kent');
GO

We’ll see the IDENTITY column continue to increase from where it left off. This happens if we delete one row or every row in the table.

If we use truncate instead, the IDENTITY will be reset:

TRUNCATE TABLE TestTable;
GO

INSERT INTO TestTable
VALUES ('Kent');
GO

We’ll see the IDENTITY back at 1.

The IDENTITY will not be reset with DELETE but will be reset with TRUNCATE.

Can Truncate be Rolled Back?

Yes! Using our same test data from above, let’s confirm this:

First, we can run a select to view our five records. We can then run our BEGIN TRAN and TRUNCATE to remove data. Running a SELECT again will return no data. But if we roll that transaction back and SELECT again, our original 5 records are back.

Thanks for reading!