How to TRUNCATE TABLE in SQL Server

What does TRUNCATE TABLE do in SQL Server?

TRUNCATE TABLE de-allocates data pages, so the table appears empty in one operation. It also resets IDENTITY seeds and keeps the table structure, constraints, and permissions intact.

What is the syntax of TRUNCATE TABLE?

The basic form is TRUNCATE TABLE schema.table_name;. SQL Server 2022+ adds PARTITIONS for partial truncation.

TRUNCATE TABLE [database_name.]schema.table_name
[WITH (PARTITIONS (partition_number | partition_range))];

When should I use TRUNCATE TABLE instead of DELETE?

Use TRUNCATE when you need a fast, full purge, do not need row-by-row logging, and the table is not referenced by a FOREIGN KEY. Use DELETE when filters, triggers, or foreign-key cascade actions are required.

How do I reset an IDENTITY column?

TRUNCATE TABLE automatically resets the identity seed to the original start value. No DBCC CHECKIDENT call is needed afterward.

Can I truncate multiple tables with foreign keys?

Not directly. Remove or disable the foreign-key constraints, TRUNCATE each child then parent table, and finally recreate or enable the constraints. Alternatively, DELETE with CASCADE can be used, though slower.

Best practices for TRUNCATE TABLE

1. Always run inside a transaction in non-production to verify results.
2. Grant ALTER permission carefully; only owners can truncate.
3. Schedule during maintenance windows to avoid blocking readers.

Common mistakes and how to avoid them

Mistake 1 – Expecting triggers to fire. TRUNCATE bypasses DML triggers. Use DELETE if trigger logic is mandatory.

Mistake 2 – Forgetting foreign-key references. SQL Server blocks truncation when the table is referenced, even if the referencing table is empty. Drop/disable the constraint first.

Full example in an ecommerce database

You need to empty the OrderItems table after exporting historical data.

-- Disable FK so we can truncate
ALTER TABLE OrderItems DROP CONSTRAINT FK_OrderItems_Orders;
GO
TRUNCATE TABLE OrderItems;
GO
-- Recreate FK
ALTER TABLE OrderItems
ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_id)
REFERENCES Orders(id);
GO

Related questions

• Does TRUNCATE reclaim disk space immediately?
• How do I truncate a partitioned table?
• What permissions are required to run TRUNCATE TABLE?