TRUNCATE vs DELETE

Use TRUNCATE instead of DELETE

If you need to clear the data in a table, use TRUNCATE instead of DELETE. TRUNCATE lock the table instead of at each row. No triggers are activated and no logs are generated resulting in faster performance.

Note: TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.

TRUNCATE requires ALTER privileges on the table.

Differences between the SQL Server DELETE and TRUNCATE Commands

1. Truncate reseeds identity values, whereas delete doesn’t.
2. Truncate removes all records and doesn’t fire triggers.
3. Truncate is faster compared to delete as it makes less use of the transaction log.
4. Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
5. Truncate is very fast (even on a very large table).

TRUNCATE Table with Foreign Key Constraint

Note: To TRUNCATE table with foreign key constraint, first find the constraint by using sp_help, delete the constraint, truncate the table, and then re-create the constraint.

Example:

sp_help tblTable01

ALTER TABLE tblChildTable01
DROP CONSTRAINT FK_tblChildTable01_tblTable01

ALTER TABLE tblChildTable01  WITH CHECK ADD  CONSTRAINT FK_tblChildTable01_tblTable01 FOREIGN KEY(Col01)
REFERENCES tblTable01 (Col01)
GO

ALTER TABLE tblChildTable01 CHECK CONSTRAINT FK_tblChildTable01_tblTable01
GO