Differences with Delete vs Truncate in SQL Server

By:   |   Updated: 2022-09-06   |   Comments (2)   |   Related: More > 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 vs truncate

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.

delete from table output

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.

truncate command ouput

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips


Article Last Updated: 2022-09-06

Comments For This Article




Tuesday, September 6, 2022 - 3:37:54 PM - Greg Robidoux Back To Top (90447)
Hi Tony, thanks for the feedback. The author updated the article to make this clear.

Thanks

Wednesday, August 24, 2022 - 4:56:49 PM - Tony Smith Back To Top (90410)
"This transaction(TRUNCATE) is not fully logged in the transaction log, so you would not be able to recover this data."

This does seem contradictory to Paul Randal's statement:

Paul Randal blogged this multiple times, saying, "They’re both(truncate and drop) fully-logged, but efficiently logged."

source: https://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth














get free sql tips
agree to terms