Skip Navigation Links
SQL Server 2012
SQL Server 2014
SQL Server 2016
Practice Test
Tip of the Day : 2 Ways to Execute Operating System Commands From SQL Server Management Studio
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:


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



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