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]
