Partitioning enhancements working with SQL Server 2016

One of the primary requirements inside databases is the ability to scale the system as the data volumes and the number of users accessing the system increases appreciably. There are a number of ways in which this can be achieved. One of the touted and inbuilt capability that was introduced in SQL Server 2005 was the ability to do partitioning on a specific table. Though in its initial introductions, there were enough challenges like ability to build using TSQL only, 1000 partitions only etc – but most of the restrictions were all done in the next couple of versions. I have loved the way such capabilities have been introduced over the years.

When SQL Server 2016 was introduced, I never felt there would be much on this fronts because the innovations that had to happen have already happened. What can one introduce or enhance in this space? I was pleasantly surprised of the fact that there was something interesting that was introduced that caught my eyes.

Look at a scenario as mentioned below. I have a large transactions table which is nicely partitioned across years. One of the requirement is to archive and move data beyond a certain point in time away from a table because a backup has been taken. In the past, I have seen people move the data to a separate table using the SWITCH mechanism and then they go about truncating or dropping the table it has been moved into.

Now that we have set the stage for this scenario – in SQL Server 2016 we allow you to truncate a partition alone. This was a welcome enhancement that I would like to show as part of this blog. Let me walk through the script:

DROP DATABASE IF EXISTS TruncDB;
GO
CREATE DATABASE TruncDB
ON PRIMARY
(NAME='TruncDB_Part1', FILENAME= 'C:\Data\TruncDB_Part1.mdf',
SIZE=2, MAXSIZE=100, FILEGROWTH=1), 
FILEGROUP TruncDB_Part2
(NAME = 'TruncDB_Part2', FILENAME = 'C:\Data\TruncDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1); 
GO

USE TruncDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TruncDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (10);
GO

--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TruncDB_PartitionScheme
AS PARTITION TruncDB_PartitionRange TO ([PRIMARY], TruncDB_Part2);
GO

Now that our basics are done for setup. Let us create a table and add some values inside each of the partitions under question.

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TruncDB_PartitionScheme (ID);
GO

--- Step 5 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO
--- Step 6: Test Data from TestTable
SELECT *
FROM TestTable;
GO

To show the before and after effect of truncate table. Here is the script that I have used, wherein we delete only the 2^nd partition.

--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

-- Works from SQL 2016 only
TRUNCATE TABLE dbo.TestTable WITH (PARTITIONS (2));


--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

As you can see the # of rows is now 0 as opposed to the previous value of 2. This is an interesting and nice little enhancement that got introduced with SQL Server 2016 under the partitioning feature. We wouldn’t see much of talk on these enhancements as these are hidden well into the product.

Will such innovations and enhancements help you in your production or dev environments? I would be curious to understand how you used partitioning inside SQL Server in your environments. Have you used the 15000 partitions? What do you do? Let me know via comments.