New SQL Server 2016 Truncate Table With Partitions

I have a table with different partitions and would like to truncate rows in the particular partitions in a table rather than truncating whole table. How can we do this?

This task is fairly easy in sql server 2016. Microsoft introduced a new clause called “With Partitions” in truncate table command.

Let’s see how it works.

For the demonstration i have created a sample database “SamplePartition” with three Filegroups FG1, FG2 and FG3.

USE Master
GO

CREATE DATABASE SamplePartition
ON PRIMARY
(NAME='SamplePartition_1',
FILENAME=
'E:\PartitionDB\FG1\SamplePartition_1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'SamplePartition_2',
FILENAME =
'E:\PartitionDB\FG2\SamplePartition_2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'SamplePartition_3',
FILENAME =
'E:\PartitionDB\FG3\SamplePartition_3.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 )
GO

Now, we have a database with three data files on three different file groups.

 Use SamplePartition
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO
-- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

1

Create partition function

Use SamplePartition
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2015-01-01', '2016-01-01')
GO

Create Partition Scheme:

Use SamplePartition
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

Create Partition Table:

Use SamplePartition
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

Inserting data to partition table:

Use SamplePartition
GO
INSERT INTO SalesArchival (SaleTime, ItemName)
SELECT '2013-03-25','Item1' UNION ALL
SELECT '2014-10-01','Item2' UNION ALL
SELECT '2015-01-01','Item1' UNION ALL
SELECT '2015-08-09','Item3' UNION ALL
SELECT '2015-12-30','Item2' UNION ALL
SELECT '2016-01-01','Item1' UNION ALL
SELECT '2016-05-24','Item3'
GO

Check the data in different partitions

Use SamplePartition
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO

2

With this we have data spread across different partitions

Let us truncate partition number 2

TRUNCATE TABLE SalesArchival
WITH (PARTITIONS(2));

I have truncated the rows in partition 2. Let’s see the result

3.png

There you go! We have truncated the rows in partition 2.

If you would like to truncate the range of partitions you can give the range like this

TRUNCATE TABLE SalesArchival
WITH (PARTITIONS(2 to 3));
Restrictions:

You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

References:

https://msdn.microsoft.com/en-us/library/ms177570.aspx 

How to create quick partition table with example

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

Advertisements

About Ramasankar

My name is Ramasankar Molleti, and I have been working on sql server over 10 years, primarily focusing on Database Administration, Architect , Development and Business intelligence of sql server. I also hold various Microsoft certifications (Microsoft Certified Information Technology Professional, Microsoft Certified Technology Specialist, and Microsoft Certified Solution Associate). Disclaimer The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights. The owner of https://sqlram.wordpress.com will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s