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.

--

--

--

Data | BI Engineer (Microsoft Stack)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

SQL SERVER LOG SHIPPING BETWEEN TWO WORKGROUP MACHINES

Create a simple responsive image gallery with HTML and CSS

Web Scraping with BeautifulSoup and Requests

Little Boxes on the Browser

5 Strategies For Quick ROI Through Faster SAP S/4HANA Implementation

Data migration or how to roll out a release without losing users

What would you do differently if you were learning to code from day 1 all over again?

Top 5 websites and tools for computer science students (Part 2)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michal Molka

Michal Molka

Data | BI Engineer (Microsoft Stack)

More from Medium

True Elasticity of Oracle Autonomous Database

Migration of PostgreSQL using Pandas

Data Engineering Good Practices to Database Queries

How to Copy Data From CSV File To POSTGRES Database?