Truncate Table Operations in SQL Server

Truncating a table is removing all the records in an entire table or a table partition. TRUNCATE table is functionally similar to DELETE table with no WHERE clause. However, TRUNCATE table is much faster than DELETE with respect to the time and the resource consumptions which we will look at in this article. TRUNCATE statement removes the data by de-allocating the data pages in the table data. This means that TRUNCATE is similar to drop and re-create the table. Also, it records only the page de-allocations in the transaction log, not the row-wise as in DELETE statement.

Setting up the Environment

Let us create a sample table and populate a few records to demonstrate different aspects of TRUNCATE by using the following T-SQL code.

CREATE TABLE SampleTable

(ID INT IDENTITY PRIMARY KEY CLUSTERED,

NAME CHAR(200),

ADDRESS CHAR(1000))

INSERT INTO [dbo].[SampleTable]

([Name] ,[Address])

VALUES

('AAA' ,'CCCC')

From the above T-SQL code, a sample table called SampleTable is created and an adequate number of sample records were populated.

To truncate the table, following T-SQL command can be executed.

1	TRUNCATE TABLE [SampleTable]

It is important to note that there is no WHERE clause in the TRUNCATE statement. TRUNCATE option is available in SQL Server all editions, Azure SQL Server and Azure data warehouse instances.

Comparison of Resources

Let us compare the resource consumptions between the TRUNCATE and DELETE statements. This was done to the same table with 500,000 records.

Statement	CPU	Reads	Writes	Duration	Row Count
TRUNCATE	0	52	0	0	0
DELETE	3,297	1,509,542	84,090	99,905	500,000

There is nothing to compare between TRUNCATE and DELETE as observed in the above table. It is obvious that TRUNCATE is much faster and use fewer resources than the DELETE statement in all aspects.

Comparison of Transaction Log File

Let us compare the usage of transaction log file during the TRUNCATE and DELETE statements.

Following screenshot shows the log size after the inserts are done to the sample table which was created before.

From the above screenshot, it can be seen that transaction log usage percentage is 0.3%.

Following is the log file usage after the DELETE statement is completed.

As you can see from the above screenshot, transaction log usage has increased to 0.7% when those records are deleted via DELETE statement.

Following is the screenshot for the log usage percentage when the data is deleted from the TRUNCATE statement.

You will see from the above screenshot that, log usage has not grown at all. This means that during the TRUNCATE will be minimally logged in the transaction log than the DELETE statement.

Since TRUNCATE statement minimally logged in the Transaction Log, TRUNCATE is faster than DELETE statement. This is due to the fact that TRUNCATE is equivalent to dropping the table and recreating it. Since TRUNCATE will not consume large transaction log resources, transaction log backup file will be less in size and will improve the performances of recovery options such as mirroring and log shipping.

IDENTITY Property during TRUNCATE TABLE

IDENTITY property is used in a table when you need to auto increase a number for a column. This means that when the first record is inserted IDENTITY column will become 1 and the next record will be 2 and so on. When entire data in the table is deleted what will happen to the next number.

When the DELETE statement is executed, previous records are counted. Let us execute the following T-SQL to get the record count and maximum number for the identity column.

SELECT MAX(ID) [Current Number],

COUNT(*) [Record Count]

FROM SampleTable

Following is the output for the above query.

You will see that though the record count is 1, the current maximum number of the identity column is 500001. This means that DELETE statement will not be impacted to IDENTITY column’s next value and DELETE statement will NOT reset the IDENTITY column even though there are no records.

Let us look at the same behavior with the TRUNCATE statement.

You can see that with the TRUNCATE statement, IDENTITY column is reset.

Transactions

Since when TRUNCATE statement is executed, fewer transactions log resources are used, rollback transaction will take less duration than DELETE statement even though, it the table has a large number of records.

Partition Truncations

TRUNCATE cannot be executed with a WHERE clause means that all records will be removed from the TRUNCATE / statement. However, partitions can be truncated as shown in the below T-SQL statement.

TRUNCATE TABLE [SampleTable]

WITH (PARTITIONS (2, 4, 6 TO 8));

From the above statement, partitions 2,4,6,7,8 will be truncated leaving the other partitions data will not be truncated.

Triggers

Though DELETE statement will trigger the DELETE trigger, it is important to note that TRUNCATE will not trigger the DELETE trigger. Therefore, if your system needs to delete trigger to fire, the TRUNCATE TABLE statement should not be issued.

Security

TRUNCATE TABLE needs minimum alter table permission to a user to. This means that DELETE table permission is not sufficient to execute the TRUNCATE statement.

Limitations

TRUNCATE statement will be limited during the following scenarios.

When a table is referenced by a foreign key constraint truncate is not possible. However, you can truncate tables where it is referenced to the same table
When a table is included in an Indexed View, TRUNCATE is not possible
If tables are included in Transactional or Merge replication, TRUNCATE is not possible

Author
Recent Posts

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka