In Part I of this article series, we discussed how to create a simple table partition based on a range of unique numbers.
In the part I of the article we did a simple partition by dividing the table into two. This article illustrates how to create a table partition by dividing the table into four parts.
A Table can be partitioned based on any column in the table. Microsoft defines that column as the partition key.
Step 1
Let us assume we have created the folder C:\Data2 and the following subfolders. [Refer Fig 1.0]
C:\Data2\Primary
C:\Data2\FG1
C:\Data2\FG2
C:\Data2\FG3
C:\Data2\FG4
Fig 1.0
Step 2
Let us assume that we have a database, “Data Partition DB2” with five different file groups, as shown below.
USE [master]
GO
/****** Object: Database [Data Partition DB] Script Date: 10/08/2006 23:09:53 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Data Partition DB2′)
DROP DATABASE [Data Partition DB2]
GO
CREATE DATABASE [Data Partition DB2]
ON PRIMARY
(NAME=’Data Partition DB Primary FG’,
FILENAME=
‘C:\Data2\Primary\Data Partition DB Primary FG.mdf’,
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG1]
(NAME = ‘Data Partition DB FG1’,
FILENAME =
‘C:\Data2\FG1\Data Partition DB FG1.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG2]
(NAME = ‘Data Partition DB FG2’,
FILENAME =
‘C:\Data2\FG2\Data Partition DB FG2.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG3]
(NAME = ‘Data Partition DB FG3’,
FILENAME =
‘C:\Data2\FG3\Data Partition DB FG3.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG4]
(NAME = ‘Data Partition DB FG4’,
FILENAME =
‘C:\Data2\FG4\Data Partition DB FG4.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
Step 3
Partitioning of the tables depends on the partition range defined by Partition Function. Let us assume that we are going to partition the table into four parts, onto four different file groups.
Execute the following T-SQL statement.
use [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
When we use LEFT clause in the range, the first partition could hold a value from negative infinity to 100, the second partition could hold values from 101 to 200, the third partition could hold values from 201 to 300 and the fourth partition could hold values from 301 to Infinity or whatever the max range of the integer value.
When we use RIGHT clause in the range, the first partition could hold a value from negative infinity to 99, the second partition could hold values from 100 to 199, the third partition could hold values from 200 to 299 and the fourth partition could hold values from 300 to Infinity or whatever the max range of the integer value.
Execute the following query to see the partition information.
SELECT * FROM sys.partition_range_values
Step 4
Partition Function is not useful unless it is associated with the proper file groups that we have created.
Let us assume that we are going to use file group [Data Partition DB FG1],
[Data Partition DB FG2], [Data Partition DB FG3], [Data Partition DB FG4] for the partition table that we are going to create. This can be created as shown below.
USE [Data Partition DB2]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);
Step 5
Now let us create the actual table that we want to partition, using the created partition scheme. The CREATE TABLE statement should contain the partition key and the partition scheme to be used. This can be created as shown below.
USE [Data Partition DB2]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [Data Partition Scheme] (ID);
Step 6
Now let us create an index on the partitioned table. An index on a table improves performance. When both the indices and the table use the same partitioning function and the same partitioning columns, the table and index are said to be aligned. This can be created as shown below.
USE [Data Partition DB2]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID)
ON [Data Partition Scheme] (ID)
Step 7
Now let us insert some data on to the table, MyTable, using the following T-SQL statements.
USE [Data Partition DB2]
go
declare @count int
set @count =-25
while @count <=100
begin
insert into MyTable select @count,getdate(),100.00
set @count=@count+1
end
set @count =101
while @count <=200
begin
insert into MyTable select @count,getdate(),200.00
set @count=@count+1
end
set @count =201
while @count <=300
begin
insert into MyTable select @count,getdate(),300.00
set @count=@count+1
end
set @count =301
while @count <=400
begin
insert into MyTable select @count,getdate(),400.00
set @count=@count+1
end
set @count =401
while @count <=800
begin
insert into MyTable select @count,getdate(),500.00
set @count=@count+1
end
Let us query the table using the T-SQL statement as shown below.
select * from MyTable
You will get the following results, shown below.
ID Date Cost
———– ———————– ———————
-25 2006-10-26 16:12:48.133 100.00
-24 2006-10-26 16:12:48.150 100.00
-23 2006-10-26 16:12:48.163 100.00
-22 2006-10-26 16:12:48.163 100.00
-21 2006-10-26 16:12:48.163 100.00
-20 2006-10-26 16:12:48.180 100.00
-19 2006-10-26 16:12:48.180 100.00
-18 2006-10-26 16:12:48.180 100.00
.
.
.
1 2006-10-26 16:12:48.303 100.00
2 2006-10-26 16:12:48.303 100.00
3 2006-10-26 16:12:48.320 100.00
4 2006-10-26 16:12:48.320 100.00
5 2006-10-26 16:12:48.320 100.00
6 2006-10-26 16:12:48.337 100.00
7 2006-10-26 16:12:48.337 100.00
8 2006-10-26 16:12:48.337 100.00
9 2006-10-26 16:12:48.350 100.00
.
.
.
.
.
795 2006-10-26 16:12:53.303 500.00
796 2006-10-26 16:12:53.320 500.00
797 2006-10-26 16:12:53.320 500.00
798 2006-10-26 16:12:53.320 500.00
799 2006-10-26 16:12:53.337 500.00
800 2006-10-26 16:12:53.337 500.00
Conclusion:
This article illustrated Data partitioning, the new feature introduced in SQL Server 2005 by Microsoft. This part of the series illustrated how to partition a table based on different ranges. The subsequent article in this series will illustrate how to modify the partition function and partition schemes and how to handle file groups, etc.
