Data partitioning in SQL Server 2005 – Part III

In Part II of this series, we saw how to partition a table into four parts based on range of unique numbers.


Part III of the article series illustrates how to partition an existing table with data into four different file groups.


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 DB3,” with five different file groups, as shown below.

use master
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Data Partition DB3′)
DROP DATABASE [Data Partition DB3]
GO
CREATE DATABASE [Data Partition DB3]
ON PRIMARY
(NAME=’Data Partition DB Primary FG3′,
FILENAME=
‘C:\Data2\Primary\Data Partition DB Primary FG3.mdf’,
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB3 FG1]
(NAME = ‘Data Partition DB3 FG1’,
FILENAME =
‘C:\Data2\FG1\Data Partition DB3 FG1.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB3 FG2]
(NAME = ‘Data Partition DB3 FG2’,
FILENAME =
‘C:\Data2\FG2\Data Partition DB3 FG2.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB3 FG3]
(NAME = ‘Data Partition DB3 FG3’,
FILENAME =
‘C:\Data2\FG3\Data Partition DB3 FG3.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB3 FG4]
(NAME = ‘Data Partition DB3 FG4’,
FILENAME =
‘C:\Data2\FG4\Data Partition DB3 FG4.ndf’,
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )

Step 3


Let us assume that we have a table on the primary file group. You could create that table by executing the following T-Sql statement.

USE [Data Partition DB3]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money ) on [primary]

Step 4


Now let us create a unique clustered index on the table by exeucting the following sql statement.

USE [Data Partition DB3]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID) on [PRIMARY]

Step 5


Now let us insert some data onto the table, MyTable, using the following T-SQL statements.

USE [Data Partition DB3]
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 shown below.

select * from MyTable
ID Date Cost
———– ———————– ———————
-25 2006-11-09 15:12:01.177 100.00
-24 2006-11-09 15:12:01.207 100.00
-23 2006-11-09 15:12:01.207 100.00
-22 2006-11-09 15:12:01.223 100.00
-21 2006-11-09 15:12:01.223 100.00
-20 2006-11-09 15:12:01.223 100.00
-19 2006-11-09 15:12:01.240 100.00
.
.
.
0 2006-11-09 15:12:01.347 100.00
1 2006-11-09 15:12:01.347 100.00
2 2006-11-09 15:12:01.363 100.00
3 2006-11-09 15:12:01.363 100.00
.
.
792 2006-11-09 15:12:06.270 500.00
793 2006-11-09 15:12:06.270 500.00
794 2006-11-09 15:12:06.287 500.00
795 2006-11-09 15:12:06.287 500.00
796 2006-11-09 15:12:06.287 500.00
797 2006-11-09 15:12:06.300 500.00
798 2006-11-09 15:12:06.300 500.00
799 2006-11-09 15:12:06.317 500.00
800 2006-11-09 15:12:06.317 500.00

Now let us query the parition information of this table by executing the following query.

select * from sys.partitions where object_name(object_id)=’MyTable’

You can see the result as shown below [Refer Fig 1.1]. It is obvious that all of the 826 rows are in the same partition.




Fig 1.1


Step 6


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.

use [Data Partition DB3]
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 values 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 rang of the integer value.


When we use RIGHT clause in the range, the first partition could hold values 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 rang of the integer value.


Execute the following query to see the partition information.

SELECT * FROM sys.partition_range_values

Step 7


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 DB3 FG1],


[Data Partition DB3 FG2], [Data Partition DB3 FG3], [Data Partition DB3 FG4] for the partition table that we are going to create. This can be created as shown below.

USE [Data Partition DB3]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB3 FG1], [Data Partition DB3 FG2], [Data Partition DB3 FG3],[Data Partition DB3 FG4]);

Step 8
Now, let us move the table to the new partition by using the following TSQL command.

Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) )

Now, let us query the partition information by using the following query.

select * from sys.partitions where object_name(object_id)=’MyTable’

You can see the result as shown below [Refer Fig 1.2]. It is obvious that all of the 826 rows are spead across all four file groups, based on the partition range.




Fig 1.2


Microsoft provided the following catalog views to query information about partition.

select * from sys.partition_functions
select * from sys.partition_parameters
select * from sys.partition_range_values
select * from sys.partition_schemes
select * from sys.partitions

Conclusion:


This article illustrated Data partitioning, the new feature introduced in SQL Server 2005 by Microsoft, and how to partition an existing table with data.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles