Delete vs Truncate SQL Server Quick Reference Guide

By: Aubrey Love | Updated: 2022-09-06 | Comments (4) | Related: > TSQL

Problem

As a DBA, you may be asked to remove specific records or all the data from a table. An example would be to remove all data older than X years because it's no longer relevant. Perhaps you're asked to remove all data from a table each year to log only the current year's data. In either case, you need to keep the table and the table structure in place. This brings us to the question, should I use DELETE or TRUNCATE? How can I determine which command is the correct one for the SQL query?

Solution

In this Microsoft SQL Server tutorial, we will discuss the differences between the DELETE command and the TRUNCATE command. Once you understand the similarities and differences, you can make a better decision on which one is the best to use in your situation. I have included a side-by-side comparison chart for your convenience that shows the differences between each command.

SQL DELETE Command

As shown in the "Quick Reference Table" (below), the DELETE statement is a DML command (Data Manipulation Language). The DELETE statement allows the use of a WHERE clause to remove only the rows or tuples that meet the conditions set in the WHERE clause. Without a WHERE clause, all rows (tuples) are deleted from the table. All the deleted rows will be logged in the transaction log, because this is a fully logged transaction you would be able to recover this data if needed.

SQL TRUNCATE Command

TRUNCATE is a DDL (Data Definition Language) command. It's used to delete all rows in a table and cannot include a WHERE clause. The TRUNCATE command is much faster than the DELETE command since it's deleting (truncating) all the rows as a block instead of row by row.

If use a transaction with the TRUNCATE command, you can use ROLLBACK to undo the truncate. The TRUNCATE command will not allow a ROLLBACK option to undo the deleted rows in a table after the COMMIT function has been applied.

The TRUNCATE operation is fully logged in the transaction log, but does not take up as much space in the log as a DELETE operation that deletes all rows. The image below shows the log file size after the DELETE command and TRUNCATE command respectfully. Both tables were identical in structure and quantity of data, as indicated by the “mdf” file size in the image below.

DELETE Statement vs TRUNCATE Statement Quick Reference

The following table is a quick reference guide that you can use for easy access. Although it's not all-inclusive of the differences between the DELETE and TRUNCATE commands, it does cover a lot of the major items between the two commands.

DELETE	Vs.	TRUNCATE
DELETE is a DML command.	SQL Language Type	TRUNCATE is a DDL command.
The DELETE command can contain a WHERE clause to delete only certain rows or tuples.	Definition	The TRUNCATE command is used to delete all data from a table. You cannot use a WHERE clause to filter certain rows or tuples.
The DELETE command sets locks on every record, requiring more locks and resources than the TRUNCATE command.	Locks and Resources	The TRUNCATE command only requires a lock at the table level not on each row.
If you use a BEGIN transaction, you can ROLLBACK the command.	COMMIT/ROLLBACK	If you use a BEGIN transaction, you can ROLLBACK the command.
The DELETE command will record the data of the deleted rows in the transaction log.	Transaction Log Use	The TRUNCATE command only records the deleted table as a whole in the transaction log.
The DELETE command will activate any triggers applied to the table, i.e., an audit trigger, unless triggers are disabled first.	Triggers	The TRUNCATE will not activate any triggers applied to the table. So, audit triggers to log who deleted a row will not fire.
The DELETE command will not reseed the identity values for the table.	Identity Columns	The TRUNCATE command will reseed (reset) the identity values for the table.
Having foreign keys and/or indexed views has no effect on the DELETE command as long as the rows you are trying to delete are not referenced by other tables.	Foreign Keys and Indexed views	You cannot use the TRUNCATE command if the table is referenced by a foreign key or any indexed views.

DELETE vs TRUNCATE Table Statement

I have two tables, each containing 15,000,000 (15 million) rows of data in six columns. The two tables are identical in structure and data.

In this first example, we will use the DELETE command to get the execution plan and the total run time. Here is the syntax:

DELETE FROM Sales.Customers2;
GO

The total time to DELETE 15 million rows of data took 118 seconds. We can also see what the execution plan looks like.

In this example, we will use the TRUNCATE command on a table that is an exact copy of the previous table. Here is the syntax:

TRUNCATE TABLE Sales.Customers3;
GO

The total time to TRUNCATE 15 million rows of data took less than 1 second. Note, that when using the TRUNCATE command an execution plan is not produced.

DELETE vs TRUNCATE SQL Command Summary

We covered the basics of the DELETE and TRUNCATE commands, touching on the pros and cons of each command in the Microsoft SQL Server DBMS. I also provided a printable table (cheat sheet) for your convenience.

So, which option is right for you? Well, it depends on your needs. If you only need to delete certain rows (tuples) from a table, then you will need to use the DELETE command. However, if you need to delete all the records in a table, then you should use the TRUNCATE command as long as all of the above rules apply to your situation.

Next Steps

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-09-06

Differences with Delete vs Truncate in SQL Server