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:
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.