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.
|