SQL SERVER — truncate partition, delete rows without partitions

Today, I show you three examples of deleting rows and it’s performance. Process of deleting rows on a non-partitioned table containing a different indexes setup and truncating a partition on a partitioned table.

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

A table structure

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

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

33.561.170 records have been deleted in 7:59 min.

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

33.561.170 records have been deleted in 00:27 min.

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

Let’s create a 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'
 );

A partition schema assigned to the partition function:

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

An index assigned to the partition schema:

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

We can check the partitions setup.

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 the partition.

Operation were finished instantaneous.