In this article I will explain the difference between
DELETE and
TRUNCATE commands in
SQL Server database.
Database
I have made use of the following table Customers with the schema as follow.
![Difference Between DELETE and TRUNCATE in SQL Server]()
I have already inserted few records in the table.
![Difference Between DELETE and TRUNCATE in SQL Server]()
Note: You can download the database table SQL by clicking the download link below.
DELETE Statement
The DELETE statement is used to remove rows from a table based on a specified condition.
It is a DML (Data Manipulation Language) command and is used, when we specify the row that we want to remove or delete from the table.
The DELETE command can contain a WHERE clause.
If the WHERE clause is used with the DELETE command, then it removes or deletes only those rows that satisfy the condition.
Otherwise by default, it removes all the rows from the table.
Note: DELETE command used only for deleting data from a table, not to remove the table from the database.
DELETE statement deletes data from Table without resetting the Table's identity to its seed value if there is an identity column.
Syntax
DELETE FROM TableName
WHERE Condition;
The DELETE statement will remove specific rows that match a condition.
For example:
DELETE FROM Customers
WHERE CustomerId = 4;
Screenshot
![Difference Between DELETE and TRUNCATE in SQL Server]()
If no condition is specified, it will remove all rows.
Screenshot
![Difference Between DELETE and TRUNCATE in SQL Server]()
Key Characteristics of DELETE Statement
Transaction Log – DELETE operations are fully logged in the transaction log. This means each row deletion is recorded, which can be useful for auditing and recovery purposes.
Trigger Activation – DELETE statements can activate DELETE triggers, if they are defined on the table. Triggers allow for additional processing or validation when rows are deleted.
Performance – Deleting rows one at a time and logging each deletion can make DELETE operations slower, especially for large datasets.
Space Deallocation – After deleting rows, the space is not immediately reclaimed by SQL Server. It remains allocated to the table until a REBUILD or SHRINK operation is performed.
Foreign Key Constraints – DELETE operations respect foreign key constraints. If there are related records in other tables, you must handle these constraints explicitly to avoid errors.
TRUNCATE Statement
The TRUNCATE statement is used to remove all rows from a table quickly and efficiently.
It is a DDL (Data Definition Language) command and is used to delete all the rows or tuples from a table.
Unlike the DELETE command, the TRUNCATE command does not contain a WHERE clause.
In the TRUNCATE command, the transaction log for each deleted data is not recorded.
Unlike the DELETE command, the TRUNCATE command is fast. We cannot roll back the data after using the TRUNCATE command.
TRUNCATE statement deletes all data from Table without resetting the Table's identity to its seed value.
Syntax
TRUNCATE TABLE TableName;
TRUNCATE statement removes all rows from a table without the need for a condition.
TRUNCATE TABLE Customers;
Screenshot
![Difference Between DELETE and TRUNCATE in SQL Server]()
Key Characteristics of TRUNCATE Statement
Transaction Log – TRUNCATE operations are minimally logged. Instead of logging each row deletion, SQL Server logs the Deallocation of the data pages. This results in a smaller transaction log and faster performance for large tables.
Trigger Activation – TRUNCATE does not activate DELETE triggers. This means that any logic defined in DELETE triggers will not be executed.
Performance – Because TRUNCATE is minimally logged and does not scan individual rows, it is generally faster than DELETE for large tables.
Space Deallocation – TRUNCATE releases the space allocated to the table immediately, returning it to the database for reuse.
Foreign Key Constraints – TRUNCATE cannot be executed if the table is referenced by a foreign key constraint. To truncate a table with foreign key relationships, you must either drop the foreign key constraints or use DELETE instead.
Reseed Identity Column – When TRUNCATE is used, the identity column (if present) is reset to its seed value. For example, if the table has an identity column starting at 1, it will restart at 1 after truncation.
Conclusion
Choosing between DELETE and TRUNCATE depends on the specific requirements of your operation and you can remove the rows appropriately by using the DELETE and TRUNCATE commands correctly.
Use DELETE command when you need to remove specific rows, respect foreign key constraints, or activate triggers.
The TRUNCATE command must be used with caution because it deletes all of the table’s records and when you need to quickly remove all rows from a table and reclaim space efficiently, and when there are no foreign key constraints to consider.
It totally depends on the user requirement, for removing specific rows, use DELETE.
And for remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE.
Understanding these differences will help you make informed decisions and optimize your database operations.
