DELETE vs TRUNCATE in SQL Server

Both DELETE and TRUNCATE TABLE remove rows from a table, and both leave the table itself (columns, indexes, constraints) in place. That's where the similarity ends. They work in very different ways inside SQL Server, and that affects which rows you can remove, identity values, triggers, foreign keys and speed.

Instead of only listing the differences, this article proves each one with a small order-management example. Every output below was produced by running the script on SQL Server (2025 Express edition); you can run it yourself in SSMS.

The test setup

Two related tables, Customers and Orders, plus an OrderAudit table. A trigger writes an audit row every time an order is deleted, which is a very common requirement in real systems.

CREATE TABLE Customers (CustomerId INT PRIMARY KEY, Name NVARCHAR(50));

CREATE TABLE Orders (
    OrderId    INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL REFERENCES Customers(CustomerId),   -- foreign key
    Amount     DECIMAL(10,2),
    Status     NVARCHAR(20));

CREATE TABLE OrderAudit (AuditId INT IDENTITY, Message NVARCHAR(200));
GO

-- Log every deleted order
CREATE TRIGGER trg_Orders_Delete ON Orders AFTER DELETE AS
    INSERT INTO OrderAudit(Message)
    SELECT CONCAT('Deleted order ', OrderId) FROM deleted;
GO

INSERT INTO Customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO Orders (CustomerId, Amount, Status) VALUES
    (1, 1200, 'Delivered'), (1, 450, 'Cancelled'), (2, 3100, 'Delivered'),
    (2, 780, 'Cancelled'), (1, 2250, 'Pending');

1. DELETE can remove selected rows; TRUNCATE cannot

The business wants to clear out cancelled orders only:

DELETE FROM Orders WHERE Status = 'Cancelled';

SELECT OrderId, CustomerId, Amount, Status FROM Orders;
SELECT Message FROM OrderAudit;

(2 rows affected)
(2 rows affected)

Two things happened: the two cancelled orders were removed, and the trigger fired, which is why there are two "(2 rows affected)" messages (one for the delete, one for the audit insert). The same request with TRUNCATE is simply not valid SQL:

TRUNCATE TABLE Orders WHERE Status = 'Pending';

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.

TRUNCATE is all or nothing. It always removes every row of the table.

2. TRUNCATE resets the identity; DELETE does not

Delete all orders with DELETE, then add a new one:

DELETE FROM Orders;
INSERT INTO Orders (CustomerId, Amount, Status) VALUES (1, 999, 'Pending');
SELECT OrderId, Amount FROM Orders;

Even though the table was empty, the new order got 6: DELETE does not touch the identity counter. Now the same with TRUNCATE:

TRUNCATE TABLE Orders;
INSERT INTO Orders (CustomerId, Amount, Status) VALUES (1, 999, 'Pending');
SELECT OrderId, Amount FROM Orders;

TRUNCATE resets the identity to its seed (1), as if the table were new. This matters a lot: if other systems, reports or URLs still refer to old order numbers, reusing number 1 can cause real confusion. (After a DELETE you can reset the identity yourself with DBCC CHECKIDENT ('Orders', RESEED, 0).)

3. TRUNCATE does not fire DELETE triggers

Count the audit rows after both operations:

SELECT COUNT(*) AS AuditRows FROM OrderAudit;

The trigger logged 2 rows for the cancelled orders and 3 rows for "DELETE all", so 5 in total. The TRUNCATE also removed an order (order 6), but no audit row was written. TRUNCATE does not delete rows one by one, so row-level DELETE triggers never run.

If your audit, history or sync logic lives in a DELETE trigger, using TRUNCATE silently bypasses it. This is one of the most dangerous differences in real projects.

You may also have noticed that TRUNCATE printed no "(n rows affected)" message, while every DELETE did. TRUNCATE doesn't report a row count, because it never counts rows.

4. TRUNCATE is not allowed on a table referenced by a foreign key

Orders.CustomerId references Customers. Try to truncate the parent table:

TRUNCATE TABLE Customers;

Msg 4712
Cannot truncate table 'dbo.Customers' because it is being referenced by a FOREIGN KEY constraint.

This fails even if no order references any customer. SQL Server doesn't check the rows; the mere existence of the foreign key blocks TRUNCATE. With DELETE, rows that are not referenced can be removed, and referenced rows give a normal foreign-key violation. To truncate a parent table you must first drop the foreign key (and recreate it afterwards).

5. Both can be rolled back

A popular myth says "TRUNCATE cannot be rolled back". Let's test it:

SELECT COUNT(*) AS RowsBefore FROM Orders;          -- 3

BEGIN TRAN;
    TRUNCATE TABLE Orders;
    SELECT COUNT(*) AS RowsInsideTran FROM Orders;  -- 0
ROLLBACK;

SELECT COUNT(*) AS RowsAfterRollback FROM Orders;   -- ?

All three rows came back. In SQL Server, TRUNCATE is transactional: it logs which data pages were deallocated, so a rollback can restore them. What you cannot do is roll it back after it has been committed, which is equally true for DELETE. (Some other databases, such as older MySQL engines and Oracle, treat TRUNCATE differently, which is where the myth comes from.)

6. Why TRUNCATE is faster: logging

• DELETE removes rows one at a time and writes every deleted row to the transaction log, takes row or page locks, and fires triggers. Deleting 10 million rows can create a huge log and take minutes.
• TRUNCATE deallocates the data pages and logs only those page deallocations (minimal logging). It takes a table-level lock and finishes almost instantly, regardless of the number of rows.

That's also why a big DELETE can fill the transaction log, while TRUNCATE barely touches it.

Comparison table

Which one should you use?

• Remove some rows (cancelled orders, old logs, one customer's data): DELETE with a WHERE. For millions of rows, delete in batches (DELETE TOP (10000) ... in a loop) to keep the log and locks small.
• Empty a staging or import table before loading new data: TRUNCATE. It's fast, and nothing depends on its identity values or triggers.
• Reset a test database to a clean state: TRUNCATE (child tables first), or DELETE where foreign keys block it.
• Tables with audit triggers, or referenced identity numbers: DELETE, so triggers run and numbers are never reused.

Before running either on a real database: take a backup, run it first inside BEGIN TRAN ... ROLLBACK to check the effect, and double-check the WHERE clause. A DELETE without a WHERE removes everything, exactly like TRUNCATE.

Interview questions

1. What is the difference between DELETE and TRUNCATE?

DELETE is a DML command that removes rows one by one, can use a WHERE clause, logs every row, fires triggers and keeps the identity counter. TRUNCATE is a DDL command that removes all rows by deallocating data pages, is minimally logged and much faster, doesn't fire DELETE triggers, resets the identity, and can't be used on tables referenced by a foreign key.

2. Can TRUNCATE be rolled back?

Yes, in SQL Server, if it runs inside an explicit transaction that hasn't been committed. The deallocated pages are logged, so ROLLBACK restores all rows.

3. Why can't we truncate a table that has a foreign key pointing to it?

Because TRUNCATE doesn't check rows individually, so it can't verify that no child rows reference the data. SQL Server blocks it whenever any foreign key references the table, with error 4712, even if the child table is empty.

4. After DELETE FROM Orders, what will the next identity value be?

It continues from the last value used (in our test, 6). Only TRUNCATE, or DBCC CHECKIDENT ... RESEED, resets it.

5. Does TRUNCATE fire triggers?

No. DELETE triggers do not fire for TRUNCATE, because it doesn't delete individual rows.

6. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes some or all rows. TRUNCATE removes all rows but keeps the table. DROP TABLE removes the table itself: its structure, data, indexes, constraints and triggers.

7. Which permission is needed for TRUNCATE?

At least ALTER permission on the table (or membership in roles such as db_owner or db_ddladmin). DELETE needs only DELETE permission.

Summary

• DELETE removes chosen rows (WHERE); TRUNCATE always removes all rows.
• TRUNCATE resets the identity; DELETE keeps counting.
• TRUNCATE skips DELETE triggers and is blocked by referencing foreign keys.
• Both can be rolled back inside a transaction in SQL Server.
• TRUNCATE is fast because it deallocates pages instead of logging every row.
• Use DELETE for selective or audited removal, and TRUNCATE to empty staging and test tables.