Table and Index Partitioning - Microsoft SQL Server 2012 Performance Tuning - page 316

Note that, during the whole cycle, partition 1 and partition 6 always remain empty and will not
contain any data at any given point in time. They are required to provide the sliding window
mechanism. Therefore, they have deliberately been kept empty here. This is necessary when
you are implementing the sliding window scenario, because in this scenario, we need to
purge/archive the oldest data (partition 2) by truncating the partition and merging it with
partition 1, at the same time making room for new data by splitting the last existing empty
partition into two! The reason behind always keeping the first and last partition empty is the
data movement that SQL Server may have to perform across these partitions while merging
or splitting partitions. If two non-empty partitions are merged or split, it can cause data
movement across the partitions, based on new range values from one partition to the other,
which is quite an expensive operation in terms of I/O and may take a long time depending
upon the volume of data. On the other hand, merging or splitting two empty partitions
does not cause any data movement and is thus a very fast operation. So, to avoid any
data movement across the partitions for a faster sliding window operation, we always
keep partition 1 and partition 6 empty.

Getting ready

The following is the prerequisite for this recipe:

F An instance of SQL Server 2012 Developer or Enterprise Evaluation edition

How to do it...

Follow the given steps to implement table partitioning on the DATETIME column for deleting
data and loading bulk data periodically in sliding window fashion:

1. Start SQL Server Management Studio and connect to SQL Server.

2. Execute the following T-SQL script to create the partition function

pf_Quaterly_RangeRight with RANGE RIGHT boundaries and
the partition scheme ps_Quaterly_RangeRight .
USE master
GO

--Creating Sample_DB database
--if it does not exist.
--DROP DATABASE Sample_DB
IF DB_ID('Sample_DB') IS NOT NULL
DROP DATABASE [Sample_DB]

CREATE DATABASE [Sample_DB]
ON PRIMARY
(
NAME = N'Sample_DB'