////http://www.sqlshack.com/database-table-partitioning-sql-server/
Partitioning was introduced in SQL Server 2005, it allows you to
different database files, which can be located on different disks to
improve performance. In earlier versions this was achieved by creating
separate tables on different file groups of database, and then creating a
view by UNIONing these tables to make it look like a single table.
First, let us create a Test database for partitioning with more than one file groups. I have created
[PartitionDB] with two file groups –
[PRIMARY] and
[FileGroup2], both of these groups contain a data file:
CREATE DATABASE [PartitionDB] ON
PRIMARY
( NAME = N'PartitionDB',
FILENAME = N'C:\PartitionDB\PartitionDB.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
),
FILEGROUP [FileGroup2]
( NAME = N'PartitionDB_2',
FILENAME = N'C:\PartitionDB\PartitionDB_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'PartitionDB_log',
FILENAME = N'C:\PartitionDB\PartitionDB_log.ldf' ,
SIZE = 9216KB , FILEGROWTH = 10%
)
Note: I have created both files on the same disk here, it is recommended to create files on separate disks for optimal performance.
Now we have a database to work with, let’s start with table partitioning, Partitioning a Table involves three steps:
Step 1: Creating a Partition Function – This is the
first step in creating a partitioned table, a partition function tells
SQL Server “how” to split the data. A partition function is not related
to any particular table, it is a “technique” that SQL Server will use to
split the data rows.
A partition function defines the range of values for a partition. For
example, customer order data can be partitioned according to order
date. I.e. data for current year will be in
[PRIMARY] file group, while older data will be in
[FileGroup2] file group. To achieve this we can create partition function as:
— Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION ordersPartFunc (DATE)
AS RANGE LEFT
FOR VALUES ('2010-01-01')
The
VALUES(<list of values>) define a list of boundary values for each partition, By specifying
VALUES (2010) we have created two partitions.
1. For Values < 2010 and,
2. For Values > 2010
RANGE defines to which partition the boundary values will go. It can either be specified as
RANGE LEFT or
RANGE RIGHT, depending on this the placement of boundary values is decided. Here, we have used
RANGE LEFT that means ‘2010’ will go to LEFT partition, so the partitions will contain below data:
1. Values <= 2010 and
2. Values > 2010
Step 2: Creating a Partition Scheme – Once the
partition function is created, SQL Server knows “how” to split data, but
it does not know where to put this partitioned data, this is defined by
partition scheme. Partition scheme need to be linked to a partition
function, to specify where each partition will be stored. We can create a
partition scheme linked to
ordersPartFunc as:
— Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME ordersPartScheme
AS PARTITION ordersPartFunc
TO ([FileGroup2], [PRIMARY])
Note: A Partition scheme can only be created for file groups, and not individual data files.
Each file group defined in
TO(<list of file groups>) corresponds to
VALUES(<list of values>) in partition function. This scheme defines that:
1.
[FileGroup2] will contain data for Values <= 2010 and,
2.
[PRIMARY] will contain data for Values > 2010
We have now created the partition function and partition scheme, but
we haven’t linked these to any tables yet. This can be done while
creating the Table. A partition scheme can be applied to more than one
table.
Step 3: Creating a Partitioned Table – Once he
partition scheme is defined, a table can be created using the partition
scheme. It is done by specifying the partition scheme in the “ON” clause
while creating the table. Only partition scheme need to be specified,
you don not need to specify a partition function as this is already
defined in partition scheme:
— Step 3. Creating a Partitioned Table
CREATE TABLE Table_Orders
(
OrderID INT,
Name VARCHAR(20),
OrderDate DATE
)
ON ordersPartScheme (OrderDate)
That’s all folks, data inserted to ‘Table_Orders’ will be partitioned
according to ordersPartFunc, and will be stored according to
ordersPartScheme.
Let’s insert some data to check if the partitions are created successfully.
INSERT INTO Table_Orders (OrderID, Name, OrderDate)
VALUES (1, 'Vishal', '2011-07-22')
— Will go to Partition 2 -> [PRIMARY] File Group
INSERT INTO Table_Orders (OrderID, Name, OrderDate)
VALUES (2, 'Vishal', '2010-06-22')
— Will go to Partition 1 -> [FileGroup2] File Group
INSERT INTO Table_Orders (OrderID, Name, OrderDate)
VALUES (3, 'Vishal', '2009-05-22')
— Will go to Partition1 -> [FileGroup2] File Group
SELECT OrderID, Name, OrderDate
FROM Table_Orders
Result Set:
OrderID Name OrderDate
———– ——————– ———-
3 Vishal 2009-05-22
1 Vishal 2011-07-22
2 Vishal 2010-06-22
(3 row(s) affected)
You can use
sys.partitions catalog view to check number of partitions and rows for a table as below:
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Table_Orders')
Result Set:
partition_id object_id partition_number rows
——————– ———– —————- ——————–
72057594038910976 5575058 1 1
72057594038976512 5575058 2 2
(2 row(s) affected)
sys.partitions can tell how many rows are present in each partition, you can use
$PARTITION function to identify a partition for a row.
$PARTITION can be used
$PARTITION.partition_function_name(expression):
SELECT OrderID, Name, OrderDate,
$PARTITION.ordersPartFunc(OrderDate)
AS 'Partition'
FROM Table_Orders
Result Set:
OrderID Name OrderDate Partition
———– ——————– ———- ———–
3 Vishal 2009-05-22 1
1 Vishal 2011-07-22 2
2 Vishal 2010-06-22 2
(3 row(s) affected) 
