Partitioning and simple partitioning switching

Partitioning in SQL server
In this article I would present some good SQL featuers. I got a question from a customer how they can insert data on daily basis into a table which is used quite much so the focus was to minimize the impact for the insert operation. One way to solve this issue can be to make use of the partitioning in SQL and the Partitioning switching. We use one table for staging where we load the data at any time. Then since the switching operation just take some milliseconds we can do it at any time.

CREATE TABLE [dbo].[tblTestPartition](

            [id] [int] NOT NULL,

            [datum] [datetime] NULL,

            [objekt] [varchar](50) NULL

)

GO

CREATE TABLE [dbo].[tblTestPartitionStaging](

            [id] [int] NOT NULL,

            [datum] [datetime] NULL,

            [objekt] [varchar](50) NULL

)

GO

-----------Create the filegroups------------------------

ALTER DATABASE [testDB] ADD FILEGROUP [FGTestPartition1]

GO

ALTER DATABASE [testDB] ADD FILEGROUP [FGTestPartition2]

GO

ALTER DATABASE [testDB] ADD FILEGROUP [FGTestPartition3]

GO

ALTER DATABASE [testDB] ADD FILEGROUP [FGTestPartition4]

GO

------Setup the files, one on each filegroup-----------

ALTER DATABASE [testDB] ADD FILE ( NAME = N'FileTestPartition1',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestPartition1.ndf' ,

SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FGTestPartition1]

GO

ALTER DATABASE [testDB] ADD FILE ( NAME = N'FileTestPartition2',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestPartition2.ndf' ,

SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FGTestPartition2]

GO

ALTER DATABASE [testDB] ADD FILE ( NAME = N'FileTestPartition3',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestPartition3.ndf' ,

SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FGTestPartition3]

GO

ALTER DATABASE [testDB] ADD FILE ( NAME = N'FileTestPartition4',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestPartition4.ndf' ,

SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FGTestPartition4]

GO

---------Create the Partition functions-----------------

CREATE PARTITION FUNCTION [TestPartitionRange](datetime) AS RANGE RIGHT FOR VALUES ('2015-01-01', '2014-01-01', '2013-01-01');

GO

CREATE PARTITION FUNCTION [TestPartitionRangeStaging](datetime) AS RANGE RIGHT FOR VALUES ('2015-01-01', '2014-01-01', '2013-01-01');

GO

--------Create the Partition scheme----------------------

CREATE PARTITION SCHEME [TestPartitionRangeScheme]

AS

PARTITION TestPartitionRange
TO ([FGTestPartition1], [FGTestPartition2], [FGTestPartition3], [FGTestPartition4], [Primary]);
GO

CREATE PARTITION SCHEME [TestPartitionRangeSchemeStaging]

AS

PARTITION TestPartitionRangeStaging

TO ([FGTestPartition1], [FGTestPartition2], [FGTestPartition3], [FGTestPartition4], [Primary]);

GO

------Create the clustred index to the partitions--------

CREATE CLUSTERED INDEX [CLIX_Datum] ON [dbo].[tblTestPartition]

(

            [datum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON TestPartitionRangeScheme (datum)

GO

CREATE CLUSTERED INDEX [CLIX_Datum] ON [dbo].[tblTestPartitionStaging]

(

            [datum] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TestPartitionRangeSchemeStaging (datum)
GO

With the tables and partitioning objects in place we can now add some test data.

INSERT INTO [dbo].[tblTestPartition] ([id],[datum],[objekt]) VALUES (1, '2015-01-31', 'Test'); --Partition 4

INSERT INTO [dbo].[tblTestPartition] ([id],[datum],[objekt]) VALUES (1, '2014-01-31', 'Test'); --Partition 3
INSERT INTO [dbo].[tblTestPartition] ([id],[datum],[objekt]) VALUES (1, '2013-01-31', 'Test'); --Partition 2

INSERT INTO [dbo].[tblTestPartition] ([id],[datum],[objekt]) VALUES (1, '2012-01-31', 'Test'); --Partition 1
INSERT INTO [dbo].[tblTestPartition] ([id],[datum],[objekt]) VALUES (1, '2011-01-31', 'Test'); --Partition 1

Notice that I did add data also to the partition holding the most accurate data. So if we try to do the switching now it will fail with the error:

Msg 4904, Level 16, State 1, Line 106
ALTER TABLE SWITCH statement failed. The specified partition 4 of target table 'testDB.dbo.tblTestPartition' must be empty.


So let’s remove that record, 

delete from tblTestPartition where datum = '2015-01-31 00:00:00.000'

If we do a select against the destination table we see those 4 records now.

And the data in the staging table has one row.


Now it’s time to do the magic things. We will now move the data from tblTestPartitionStaging in partition 4 to the corresponding partition in the destination table tblTestPartition. We use ALTER TABLE command.

ALTER TABLE tblTestPartitionStaging SWITCH PARTITION 4 TO tblTestPartition PARTITION 4

Now the destination table has those record below and the staging table has zero records.