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

 

 

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: