SQL SERVER — truncate partition performance

Today I show you three examples of deleting rows and it’s performance. I will compare deleting rows on non-partitioned table and truncating partition on partitioned table.

Our example is a table containing 0.75 billion records. [Date] range is 2000–01–01 to 2020–12–31.

Table structure

First example (DELETE) — non-clustered index on [Date] column and clustered on [Station] and [Alpha2] columns:

DELETE FROM [dbo].[GHCN_RSI]
WHERE [Date] BETWEEN ‘2020–01–01’ AND ‘2020–12–31’

33.561.170 records has been deleted in 7:59 min.

Second (DELETE) — clustered index on [Date] column, no additional indexes:

33.561.170 records has been deleted in 00:27 min.

Third example (TRUNCATE) — clustered index on [Date] column, partitioned table:

Let’s create partition function. One partition for each year.

CREATE PARTITION FUNCTION udf_PartitionYear(date) 
 AS RANGE RIGHT 
 FOR VALUES
 (
   '2000-01-01'
  ,'2001-01-01'
  ,'2002-01-01'
  ,'2003-01-01'
  ,'2004-01-01'
  ,'2005-01-01'
  ,'2006-01-01'
  ,'2007-01-01'
  ,'2008-01-01'
  ,'2009-01-01'
  ,'2010-01-01'
  ,'2011-01-01'
  ,'2012-01-01'
  ,'2013-01-01'
  ,'2014-01-01'
  ,'2015-01-01'
  ,'2016-01-01'
  ,'2017-01-01'
  ,'2018-01-01'
  ,'2019-01-01'
  ,'2020-01-01'
  ,'2021-01-01'
 );

Partition schema assigned to partition function:

CREATE PARTITION SCHEME ups_PartionYear 
    AS PARTITION udf_PartitionYear
    ALL TO ([PRIMARY])

Index assigned to partition schema:

CREATE CLUSTERED INDEX IX_GHCN_PARTITION_Date
 ON dbo.GHCN_PARTITION ([Date])
 ON ups_PartionYear ([Date])

We can check created partitions.

SELECT partition_id, partition_number, used_page_count, row_count FROM sys.dm_db_partition_stats 
WHERE OBJECT_ID = OBJECT_ID(‘dbo.GHCN_PARTITION’)

Let’s check where is the data in a range of year 2020.

SELECT * 
FROM sys.partition_range_values
WHERE function_id = 65536

Now, we can truncate partition.

Operation were finished instantaneous.