Sergey Nivens - Fotolia
DELETE and TRUNCATE TABLE are Transact-SQL, or T-SQL, commands that can be easy to confuse because they both have the effect of removing records from a database table in SQL Server. However, the two commands behave differently, log information differently and require different amounts of resources.
TRUNCATE TABLE is a Data Definition Language (DDL) statement. DDL is the syntax used for T-SQL commands that define data structures and database schemas. A TRUNCATE TABLE command removes all the rows from a table or specified table partition without logging each row deletion in SQL Server's transaction log file. Instead, when you use TRUNCATE TABLE, it deallocates data pages and adds an entry recording the deallocation to the transaction log -- a step that minimizes the amount of required log space.
As a DDL command, TRUNCATE TABLE locks entire tables using SQL Server's schema modification, or Sch-M, locks; it also places locks at the data page level, but not on individual rows. As a result, it uses fewer locks than the DELETE command, saving on the use of system resources. Another notable attribute of TRUNCATE TABLE is that it removes all of the pages from a table, while DELETE can leave some empty pages in place. On the other hand, using TRUNCATE TABLE doesn't affect the structure of a table, including its columns, constraints and indexes. If you want to remove a table completely from a database, you'll also have to use the DROP TABLE command.
Nonetheless, TRUNCATE TABLE is the fastest option for deleting all records from a SQL Server table -- and starting with SQL Server 2016, you can truncate a specific partition of a table by using a new TRUNCATE TABLE WITH PARTITIONS clause that has been added to the list of T-SQL commands for SQL Server. While the table partitioning feature was initially introduced in SQL Server 2008, the ability to truncate individual partitions wasn't added until SQL Server 2016.
The DELETE command is based on the Data Manipulation Language (DML), a syntax used for adding, removing, selecting or changing data in a database. DELETE removes one row from a database table at a time, and every delete operation is fully logged in the transaction log. The command activates triggers in SQL Server as it logs each row deletion, which is something TRUNCATE TABLE doesn't do, since it doesn't log individual deletions.
DELETE typically also uses row-level locks, which lock every row marked for deletion in a table. Because of the additional resource requirements with DELETE, Microsoft recommends using TRUNCATE TABLE when all of the rows in a table are being removed. The advantage of a DELETE statement, though, is that it can be used with a WHERE clause to delete a specific set of rows from a table. In addition, TRUNCATE TABLE can't be used on tables that are referenced by an external foreign key constraint, are part of an indexed view, or use transactional or merge replication.
Use T-SQL to create user-defined roles
Database recovery tool recovers selected tables only
Set-up tempdb in SQL Server 2016 to get the most out of the database