mssqltips logo

SQL Server 2016 Truncate Table with Partitions

By:   |   Updated: 2016-08-30   |   Comments (5)   |   Related: More > 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


Last Updated: 2016-08-30


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, June 04, 2019 - 6:54:07 AM - Ahmad Yaseen Back To Top

Hello Moen,

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

Best Regards


Sunday, June 02, 2019 - 4:03:19 AM - Momen Back To Top

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


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

 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

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


Wednesday, August 08, 2018 - 10:32:02 AM - haim Back To Top

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools