What happens When We Truncate a Table?

I’ve been using TRUNCATE TABLE to clear out some temporary tables in a database. It’s a very simple statement to run, but I never really knew why it was so much quicker than a delete statement. So let’s look at some facts:

  1. The TRUNCATE TABLE statement is a DDL operation, whilst DELETE is a DML operation.
  2. TRUNCATE Table is useful for emptying temporary tables, but leaving the structure for more data. To remove the table definition in addition to its data, use the DROP TABLE statement.
  3. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
  4. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So it’s important to understand that TRUNCATE is logged, just not at the row level.
  5. Because TRUNCATE TABLE is not fully logged, it cannot activate a trigger.

So it’s a lot faster as it skips logging the removal of data, which means any restore of a TRUNCATE table would have to take place from the last FULL backup. However, as TRUNCATE merely de-allocates the data pages this means that the data is still there and readable, so you can read the data pages (using some 3rd party tool like ApexSQL Recover) if you’re quick enough.

But how fast is “faster”? The below script creates a table, inserts some random data and then truncates the table with IO and TIME stats switched on. It then repeats the process but this time executes a DELETE instead of TRUNCATE.


USE tempdb
GO
-- Create table
CREATE TABLE TruncTable (ID INT, IntColumnOne INT, IntColumnTwo INT, IntColumnThree INT, UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID())
GO

CREATE CLUSTERED INDEX [TT_ID]
ON TruncTable (ID)
GO

SET NOCOUNT ON
INSERT INTO TruncTable (ID, IntColumnOne, IntColumnTwo, IntColumnThree)
SELECT RAND()*10000, RAND()*10000, RAND()*10000, RAND()*10000
GO 500000
SET NOCOUNT OFF
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
truncate table TruncTable
GO
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

DROP TABLE TruncTable

USE tempdb
GO
-- Create table
CREATE TABLE DelTable (ID INT, IntColumnOne INT, IntColumnTwo INT, IntColumnThree INT,UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID())
GO

CREATE CLUSTERED INDEX [DT_ID]
ON DelTable (ID)
GO

SET NOCOUNT ON
INSERT INTO DelTable (ID, IntColumnOne, IntColumnTwo, IntColumnThree)
SELECT RAND()*10000, RAND()*10000, RAND()*10000, RAND()*10000
GO 500000
SET NOCOUNT OFF

SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
delete from DelTable
GO
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

DROP TABLE DelTable

Running on a fairly beef server returns these results; firstly for the TRUNCATE


Beginning execution loop
Batch execution completed 500000 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.

and now the DELETE


Beginning execution loop
Batch execution completed 500000 times.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'DelTable'. Scan count 1, logical reads 53022, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1000 ms,  elapsed time = 1052 ms.

The TRUNCATE completely skips any scan; it doesn’t even touch the table, whilst the DELETE scans the table before taking any action. As the number of rows increase, so the time taken to DELETE will increase, whilst executing a TRUNCATE would take any longer.

I’m glad that I looked into how a truncate works, rather than just accepting the way that things are. Before I looked into this I didn’t realise that restoring a table that had been truncated is somewhat different from restoring data that had been deleted, and in fact whilst functionally similar, the methods used to remove the data from the database are completely different.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

2 thoughts on “What happens When We Truncate a Table?”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s