SQL Server Partitioned Tables

One feature in SQL Server that I haven’t worked with previously is partitioned tables. This feature allows you take the data inserted in one table and spread it across multiple data files for horizontal partitioning. This is useful for archiving data, perhaps storing sales data for each distinct year in a separate file. This is an Enterprise feature, so you’ll need the Enterprise or Developer edition to us this feature.
For my example, I created three secondary data files to store records on Atlanta Falcons football players. We’ll use the jersey number to partition the data into three groups.

1) Create filegroups:

alter database TestDatabase add filegroup PlayerFG1;
alter database TestDatabase add filegroup PlayerFG2;
alter database TestDatabase add filegroup PlayerFG3;

2) Create data files:
Next, we’ll create three separate secondary data files and match each one to a filegroup.

alter database TestDatabase add file(name = 'PlayerDF1', FileName = 'C:\SQLData\PlayerDF1.ndf') to filegroup PlayerFG1;
alter database TestDatabase add file(name = 'PlayerDF2', FileName = 'C:\SQLData\PlayerDF2.ndf') to filegroup PlayerFG2;
alter database TestDatabase add file(name = 'PlayerDF3', FileName = 'C:\SQLData\PlayerDF3.ndf') to filegroup PlayerFG3;

3) Create partition function:
We’ll need a function that determines how we’ll split the records into each of the three groups. We’ll split the jersey numbers into three groups: 1-32, 33-63, 64-99.
When we specify ‘left’ for the range, that means that we’re specifying each value will mark the upper limit of the group. So here we specify 32 and 63 for the ranges. With range left, 32 will belong to the first group, 63 belongs to the second group, and everything over 63 goes to the third group.
Alternatively, we could specify range right where each value denotes the starting value of a group.

create partition function PlayerPartition (tinyint) as
range left for values (32, 63);

4) Create partition scheme:
Next, we create a partition scheme. This connects the partition function that breaks our data into one of three groups and maps that to a filegroup.

create partition scheme PlayerScheme as
partition PlayerPartition to (PlayerFG1, PlayerFG2, PlayerFG3);

5) Create table to partition:
Instead of designating a filegroup for the table, we’ll connect the table to the partition scheme, and we’ll also denote the column that we’ll use to partition the records.

create table Player (
JerseyNumber tinyint not null primary key,
FirstName varchar(20) not null,
LastName varchar(20) not null,
Position char(2) not null 
)
on PlayerScheme(JerseyNumber);

6) Insert Data
We’ll insert records at the range borders just to show which partition the record goes to.

insert into Player(JerseyNumber, FirstName, LastName, Position) values (2, 'Matt', 'Ryan', 'QB');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (11, 'Julio', 'Jones', 'WR');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (32, 'Jacquizz', 'Rodgers', 'RB');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (55, 'Paul', 'Worrilow', 'LB');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (63, 'Justin', 'Blalock', 'LG');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (84, 'Roddy', 'White', 'WR');
insert into Player(JerseyNumber, FirstName, LastName, Position) values (95, 'Jonathan', 'Babineaux', 'DT');

7) View Records
We’ll use the $PARTITION function to show which of the three partitions that the record goes to.

select JerseyNumber, FirstName, LastName, Position, $PARTITION.PlayerPartition(JerseyNumber) as FileGroupId from Player;

Here’s a link to the entire SQL Script for this example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: