Skip Navigation Links
Home
Functions
Tips & Tricks
SQL Server 2005
SQL Server 2008
SQL Server 2012
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Differences Between DELETE and TRUNCATE Commands
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The DELETE command and the TRUNCATE command both are used to delete data from a table.  Both commands will only delete the data of the specified table and not the structure of the table.

The DELETE command removes rows from a table or a view and has the following basic syntax:

DELETE FROM <Table or View Name>
WHERE <Search Condition>

On the other hand, the TRUNCATE command removes all rows from a table without logging the individual row deletions, and has the following basic syntax:

TRUNCATE TABLE <Table Name>

Here’s a summary of the differences between the DELETE command and the TRUNCATE command.

DELETE

TRUNCATE

Logged operation on a per row basis which means the deletion of each row is logged and physically deleted.

Logging is done by the deallocation of data pages in which the data exists.

Allows the deletion of rows that does not violate a foreign key constraint.

Does not allow the truncation of a table that is referenced by a foreign key constraint.  The foreign key constraint needs to be dropped first, then TRUNCATE the table, and then re-create the constraint.

Does not reset the identity column to the default seed value.

Resets any identity column in the truncated table to the default seed value.

Allows the conditional deletion of rows by specifying the condition in the WHERE clause.

Deletes all rows in the table and cannot specify a conditional deletion of rows.

The DELETE command is a Data Manipulation Language (DML) Command.

The TRUNCATE command is a Data Definition Language (DDL) Command.

Fires any DELETE TRIGGER defined on the table where the rows are deleted.

Since the TRUNCATE command is a DDL command, any DELETE TRIGGER is not fired.

Back to Tip of the Day List Next Tip