I want to give everyone a practical approach to partitioning and how to implement it for our database tables. In this article, we are going to take a look at creating partitions on existing table and also creating an automated way of Implementing an Automatic Sliding Window in a partitioned table.
More details about automatic sliding window can be found in How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005 (MSDN).
Creating Partition on Existing Tables
Partitioning a table is dividing a very big table into multiple parts. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. Some advantages of partitioning can be found in Partitioned Tables and Views.
For this exercise, we need a database with two filegroups, The Script below creates one database, two filegroups, and stores these filegroups on two different drives and the table we are going to create will span across these two filegroups.
CREATE DATABASE [PartitionDB]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'PartitionDB', FILENAME = N'E:\SQLServerData\PartitionDB.mdf', SIZE = 4096KB, FILEGROWTH = 1024KB ),
FILEGROUP [SECONDARY]
(NAME = N'PartitionDBArchive', FILENAME = N'F:\SQLServerData\PartitionDBArchive.ndf', SIZE = 4096KB, FILEGROWTH = 1024KB )
LOG ON
(NAME = N'PartitionDB_log', FILENAME = N'E:\SQLServerData\PartitionDB_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%)
GO
USE [PartitionDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups
WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [PartitionDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GOOur database now has two filegroups: the PRIMARY, which is stored on the E: drive, and SECONDARY filegroup stored on the F: drive. The plan is to have current transactions on a much faster disk (in this case the E: drive) and historical data on a much cheaper disk (in this case the F: drive).
Let's create a sample table, FactResellerSales, in our PartitionDB from AdventureworksDW2014. Please note in order for the below script to execute successfully, your instance should already have AdventureWorksDW2014 attached with the FactResellerSales table.
USE [PartitionDB] GO Select * INTO FactResellerSales FROM [AdventureWorksDW2014].dbo.FactResellerSales GO
Now that we have our FactResellerSales Table created in PartitionDB, let's run the below query to see the existing partitions on this table and the number of rows in this partition.
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.name LIKE '%FactResellerSales%'
As you see we have one partition with all rows on this partition.
Now let's run some queries to create the PartitionFunction and PartitionSchema. In this exercise, we are going to create the partition based on the date column. The idea of the partition function is to create two partitions. One partition stores all the records where the partition column value < 2016-01-01, and the other partition stores all the values >= 2016-01-01. Run the below script to create a partition function
CREATE PARTITION FUNCTION [myPartitionRange] (DATETIME) AS
RANGE RIGHT FOR VALUES ('2016-01-01')
GORun the below script to create a partition scheme on the two filegroups, PRIMARY and SECONDARY, that were created along with the creation of the database.
CREATE PARTITION SCHEME myPartitionScheme AS PARTITION [myPartitionRange] TO ([SECONDARY],[PRIMARY])
Now, the most important step is creating a clustered index on the FactReseller sales table and assigning the Partition Scheme to that index and table. In this example, partitioning happens on the OrderDate Column in the FactResellerSales Table.
A point to note here is a partition on an existing table needs a clustered index to be created on the column on which partitioning has to happen. If there is a clustered index on a column other than column we are going to partition, we need to drop the index on that column.
CREATE CLUSTERED INDEX IX_TABLE1_partitioncol
ON FactResellerSales (OrderDate)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON myPartitionScheme(OrderDate)Once the index is created, run the below script once again to see the partitions on our table. Now you should see a new partition created to support our incoming data for this year. All the existing data will be moved to partition_number 1 because all the OrderDates in our table are less than 2016-01-01.
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.name LIKE '%FactResellerSales%'
Run the below script to insert three records with OrderDates of 2016-01-01, 2016-01-02 and 2016-01-03. Please note the earlier in orderdate in the three records we are inserting.
INSERT INTO FactResellerSales (ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, PromotionKey, CurrencyKey ,SalesTerritoryKey , SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount , ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate) VALUES (592, 20160101, 20160101, 20160101, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2016-01-01 00:00:00.000', '2016-01-01 00:00:00.000', '2016-01-01 00:00:00.000') , (592, 20160101, 20160101, 20160101, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2016-01-02 00:00:00.000', '2016-01-02 00:00:00.000', '2016-01-02 00:00:00.000') , (592, 20160101, 20160101, 20160101, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2016-01-03 00:00:00.000', '2016-01-03 00:00:00.000', '2016-01-03 00:00:00.000')
Run the below query once again to see number of records on each partition
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.name LIKE '%FactResellerSales%'
As you see, three new records are inserted into the 2nd partition created.
Implement an Automatic Sliding Window in a Partitioned Table.
Let's assume that we started getting data for the year 2017. Our task is to merge the existing partition, which has data for 2016 to the old partition and insert data for the order year 2017 into a new partition. We need to a create stored procedure with below statements and run them yearly once as our partitions are year based. Run all three ALTER statements in a TRANSACTION to make sure you don't leave your table partitions in an inconsistent state.
/* Here you need to write a function DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)
to get current years firstday, as I am using a future date I hardcoded it
*/DECLARE @CurrentYear DATETIME='2017-01-01'
DECLARE @PrevMax DATETIME=(SELECT CONVERT(DATETIME,Value) FROM sys.partition_functions f
INNER JOIN sys.partition_range_values r
ON f.function_id = r.function_id
WHERE f.name = 'myPartitionRange')
IF @PrevMax<@CurrentYear
BEGIN TRY
BEGIN TRAN
ALTER PARTITION FUNCTION myPartitionRange()
MERGE RANGE (@PrevMax)
ALTER PARTITION SCHEME myPartitionScheme
NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION myPartitionRange()
SPLIT RANGE (@CurrentYear)
COMMIT TRAN
PRINT 'COMITIINGGGGGG'
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK TRAN
END CATCH
Run the below statement once again to check state of our partitions. You should see data for 2016 data is now merged into the old partition, and a new partition is ready for 2017 data.
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.name LIKE '%FactResellerSales%'
Now run some insert statements to insert new records for 2017, and you should see these new records moved to the newly created partition.
INSERT INTO FactResellerSales (ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount , ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate) VALUES (592, 20170101, 20170101, 20170101, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2017-01-01 00:00:00.000', '2017-01-01 00:00:00.000', '2017-01-01 00:00:00.000') , (592, 20170102, 20170102, 20170102, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2017-01-02 00:00:00.000', '2017-01-02 00:00:00.000', '2017-01-02 00:00:00.000') , (592, 20170103, 20170103, 20170103, 490, 281, 16, 100, 4, 'SO71952', 42, 1, 3, 20, 60, 0, 0, 50, 60, 2, 0, 0, '9490-4552-81', 'PO9715163911', '2017-01-03 00:00:00.000', '2017-01-03 00:00:00.000', '2017-01-03 00:00:00.000') GO
For one last time, run the below statement once again to see new records are inserted into newly created partition
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows] FROM sys.partitions p INNER JOIN sys.objects o ON o.object_id=p.object_id INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id WHERE o.name LIKE '%FactResellerSales%'
Summary
I hope this article is informative and clarified some of the partitioning questions, please pass on the feedback in case you like it. I have uploaded the files to the Resources section below if you want to download and run them.
