💠🛠️SQL Server: DELETE vs. TRUNCATE TABLE Cheat Sheet

The comparison between the DELETE and TRUNCATE TABLE commands resumes to more than saying that one method is faster than the other or that one should always use TRUNCATE TABLE when deleting all the records from a table, which typically is not advisable in production environments. I tried to provide an overview of the two commands, though this should be considered as "work in progress".

 
Disclaimer: please refer to the SQL Server Docs for the complete set of features broken down on version.

 

	DELETE	TRUNCATE TABLE
Definition	DML command that removes one or more rows from a table or view	DDL command that removes all rows from a table or specified partitions of a table, without logging the individual row deletions
Scope	tables, views, memory-optimized tables, common table expressions, MERGE, sp_MSforEachTable, linked servers	tables, partitions
Behavior	• removes rows one at a time and records an entry in the transaction log for each deleted row   - for big tables the transaction log fills fast and may reach its limit • fully logged ⇒rollback supported • executed using a row lock, though locks might be escalated to a larger scope ⇒ performance may degrade • [views] deletes the records only from the base table • allows outputting the deleted records (via OUTPUT clause) • [heaps] pages made empty may remain allocated ⇒ can’t be reused by other objects	• equivalent of a DROP & CREATE TABLE • uses an optimized logging mode:    - removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log    - a deferred-drop mechanism unhooks the allocations for the table and putting them on the ‘deferred-drop queue’ for later processing by a background task deallocates all the pages and extents • fully logged, however rollback supported only with explicit transactions • leaves zero pages in the table • resets the identity property
Syntax (simple form)	DELETE <table_name> FROM <database> [WHERE <search_condition>] [OPTION (<query_options>)]	TRUNCATE TABLE <table_name> [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }))]
Performance	• degrades with the numbers of records • [large tables] can cause the transaction log to become full	• the operation completes almost instantaneously • best practice because is faster and uses fewer system and transaction log resources
Constraints	• a DELETE may fail if - violates a trigger - tries to remove a row referenced by data in another table with a FOREIGN KEY constraint • TOP can’t be used in a DELETE statement against partitioned views • doesn’t reset the identity property	• can’t be used with views • can’t be used on tables:    - referenced by a FOREIGN KEY constraint, except self-references    - participate in an indexed view - published using transactional or merge replication - system-versioned temporal.    - referenced by an EDGE constraint • can’t activate a trigger • can’t be used on views & memory-optimized tables
Permissions	• [minimum] DELETE permission on target table, and SELECT permission, it if includes a WHERE clause • default to    - table owner    - members of the sysadmin fixed server role       - db_owner and db_datawriter fixed database roles • table owners & members of sysadmin, db_owner & db_securityadmin roles can transfer permissions to other users	• [minimum] ALTER permission on target table • default to    - table owner - members of the sysadmin fixed server role    - db_owner and db_ddladmin fixed database roles • permissions are not transferable • doesn’t support direct permissions (workaround: use TRUNCATE in stored procedure, and assign the required permission to it using the EXECUTE AS clause)
Scenarios	• delete a set of records from a table    - based on fix constraints    - based on records from another table    - based on a join with a source table • empty a set of tables from a database
Recommendations	• use a TRUNCATE when is safe to delete all the records ⇒ make sure that a backup or copy of the data is available • [large tables] consider dropping the indexes before performing a DELETE when this covers all or most of the data, and recreate them afterwards • [large tables] if the volume of data to be deleted is big compared with the remaining data, consider moving the data to a table with a similar structure, perform a TRUNCATE and then move the data back (see [5]) • [large tables] consider deleting data in batches with log truncation in single-user mode (be careful in production environments) • [heaps] specify the TABLOCK hint in the DELETE statement • [heaps] create a clustered index on the heap before deleting the row
Myths	•TRUNCATE TABLE is a non-logged operation (see [4]) •TRUNCATE TABLE is a minimally logged operation (see [3])
Related concepts	non-logged/minimally-logged/fully-logged operations, deferred-drop mechanism, sp_MSforEachTable stored procedure, transaction log, MERGE, DDL, DML

Resources:
[1] Microsoft SQL Docs (2022) DELETE [link]
[2] Microsoft SQL Docs (2022) TRUNCATE TABLE [link]
[3] Microsoft TechNet (2017) SQL Server: Understanding Minimal Logging Under Bulk-Logged Recovery Model vs. Logging in Truncate Operation [link]
[4] Paul Randal (2013) The Myth that DROP and TRUNCATE TABLE are Non-Logged [link]
[5] SQL-troubles (2018) ERP Systems: Dynamics AX 2009 – Deleting Obsolete Companies [link]
[6] Microsoft TechNet (2014) SQL Server: An Examination of Logging in Truncate Table Statement and Its Comparison with Delete Statement [link]