SQL Server 2016 Truncate Table with Partitions

By:   |   Comments (5)   |   Related: > Partitioning


Problem

SQL Server Database Administrators prefer using the TRUNCATE TABLE statement over DELETE statement because it is faster, minimally logged and consumes less server resources. The downside of the TRUNCATE TABLE statement is that it deletes all the table rows since there is no WHERE clause that can be added to the statement to specify the delete criteria.  So, how could we get the benefits of the TRUNCATE TABLE statement without deleting all the rows in the table?

Solution

SQL Server 2016 introduces a nice modification to the TRUNCATE TABLE statement that allows you to truncate a specific partition or set of partitions from your table while still get the advantages of the TRUNCATE TABLE statement without clearing all of the table’s data. This new filter mechanism on the TRUNCATE TABLE statement is achieved by using the WITH PARTITIONS() option specifying the partition or sets of partitions.

Let’s go through a simple demo to show us how it works. We will start by adding four new filegroups and four data files to our existing MSSQLTipsDemo database with the below script:

USE [master]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q1]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q2]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q3]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILEGROUP [Q4]
GO

USE [master]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q1_2016', FILENAME = N'D:\Data\Q1_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q1]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q2_2016', FILENAME = N'D:\Data\Q2_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q2]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q3_2016', FILENAME = N'D:\Data\Q3_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q3]
GO
ALTER DATABASE [MSSQLTipsDemo] ADD FILE ( NAME = N'Q4_2016', FILENAME = N'D:\Data\Q4_2016.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Q4]
GO

Once the database filegroups and files are ready, we will create the partitioning function to partition the data depending on the four quarters of the year as shown below:

USE [MSSQLTipsDemo]
GO
CREATE PARTITION FUNCTION PartitionByQuarter(INT) AS
  RANGE RIGHT FOR VALUES
  (20161,20162,20163,20164)
GO

The next step is to create the partitioning scheme that specifies the filegroup assigned for each partition value mentioned in the partitioning function:

USE [MSSQLTipsDemo]
GO
CREATE PARTITION SCHEME PartitionByQuarterScheme AS
  PARTITION PartitionByQuarter  TO
  (
   [Q1],
   [Q2],
   [Q3],
   [Q4],
   [PRIMARY] )
GO

We are ready now to create the new PartitionDemo2016 table that is partitioned using the previously created partitioning function and scheme based on the QuarterNum computed column’s value:

USE [MSSQLTipsDemo]
GO
CREATE TABLE [dbo].[PartitionDemo2016](
 [ID] [int] NULL,
 [ActionGUID] [nvarchar](50) NULL,
 [TS] [datetime] NULL,
 [QuarterNum]  AS (datepart(year,[TS])*(10)+datepart(quarter,[TS])) PERSISTED
) ON [PartitionByQuarterScheme] ([QuarterNum])

GO

Let’s fill the PartitionDemo2016 table with a few records to see what will happen:

USE [MSSQLTipsDemo]
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (1, N'asgdhfjflflgl', CAST(N'2016-01-15T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (2, N'djfjhgsfgdgd', CAST(N'2016-05-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (3, N'fvkifvjfvjfhy', CAST(N'2016-07-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[PartitionDemo2016] ([ID], [ActionGUID], [TS]) 
VALUES (4, N'mvfhufvhfgtdh', CAST(N'2016-10-30T00:00:00.000' AS DateTime))
GO

Once the data is successfully inserted, we will retrieve the PartitionDemo2016 table’s data to check the QuarterNum computed column’s value as follows:

USE MSSQLTipsDemo
GO
SELECT  [ID]
      ,[ActionGUID]
      ,[TS]
      ,[QuarterNum]
  FROM [MSSQLTipsDemo].[dbo].[PartitionDemo2016]


All of the data from the PartitionDemo2016 table

What should have occurred in the background is that the first record will be inserted into the first filegroup as it meets the first partition condition, and the second record will be inserted into the second filegroup as it meets the second partition condition and so on.

To make sure that occurred the right way, we will query the sys.partitions system table for the PartitionDemo2016 table to retrieve the number of records in each partition in the query below:

SELECT partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionDemo2016';
GO

The result is as follows:

Number of rows per partition

The result should not be a surprise as each record is inserted into its appropriate partition, and the first partition is empty based on how the partition function was created.

Now we will test the new addition to the TRINCATE TABLE statement. The below TRUNCATE statement is used to truncate the second and third partitions from the PartitionDemo2016 table, which will delete the data in these partitions:

TRUNCATE TABLE PartitionDemo2016
WITH (PARTITIONS (2 TO 3));
GO

Retrieving the PartitionDemo2016 table’s data again, the result will be like:

Remaining data after the truncation

Again, let’s query the sys.partitions table to check the number of records in each partition after applying the TRUNCATE TABLE statement:

SELECT partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionDemo2016';
GO

As previously mentioned, the data from the second and third partitions are deleted using the TRUNCATE TABLE statement, keeping the data untouched in the fourth and fifth partitions as shown below:

Rows per partition after the truncate

As you can see from the previous demo, the DELETE statement is not the only choice to delete specific data from your table, you can get the benefits of the TRUNCATE TABLE statement which is faster and consumes less resources and now has the ability to delete a specific partition or set of partitions without deleting all the table’s data.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, June 4, 2019 - 6:54:07 AM - Ahmad Yaseen Back To Top (81319)

Hello Moen,

For sure, it will be empty and ready for use.

Best Regards


Sunday, June 2, 2019 - 4:03:19 AM - Momen Back To Top (81294)

Can we re-use the partition after the truncate partition ? 


Saturday, October 13, 2018 - 8:08:51 AM - Ahmad Yaseen Back To Top (77936)

 Hello MAYURAKSHI,

 

Thank you for your input here.

This option is available only in Oracle. But in SQL Server, this will be performed autimatically when it is defined at the table level.

Best Regards,

Ahmad

 


Thursday, October 11, 2018 - 6:37:22 AM - MAYURAKSHI MITRA Back To Top (77921)

 What happens to child records when you Truncate Partitions? Is there any CASCADE option as well?


Wednesday, August 8, 2018 - 10:32:02 AM - haim Back To Top (77082)

This is what i need to manage partition table.

i disable all indexes and delete the partition (sql2016)

I check for how to delete old data from partition table where i do not need to old data

and i find it.

Thank a lot















get free sql tips
agree to terms