Data partitioning in SQL Server 2005 - Part IV

December 6, 2006

In Part III of this article series, we have seen how to partition an existing table into four parts based on a range of unique numbers. Part IV of this series illustrates partitioning based on date. In this article we are going to partition the existing data into four different parts based on certain dates.

Step 1

Let us assume we have the following folders

C:\Data\Primary
D:\Data\FG1
E:\Data\FG2

Use the following command to create the above listed folders. [Refer Fig 1.0]

MD C:\Data
MD C:\Data\Primary
MD D:\Data
MD D:\Data\FG1
MD E:\Data
MD E:\Data\FG2


Fig 1.0

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

Step 2

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

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

Step 3

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

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

Step 4

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

USE [Data Partition DB4]
go
declare @count int
set @count =-25
while @count <=100
begin
insert into MyTable select @count,getdate()-7,100.00
set @count=@count+1
end
set @count =101
while @count <=200
begin
insert into MyTable select @count,getdate()-14,200.00
set @count=@count+1
end
set @count =201
while @count <=300
begin
insert into MyTable select @count,getdate()-21,300.00
set @count=@count+1
end
set @count =301
while @count <=400
begin
insert into MyTable select @count,getdate()-28,400.00
set @count=@count+1
end
set @count =401
while @count <=800
begin
insert into MyTable select @count,getdate()-28,500.00
set @count=@count+1
end
go

Let us query the table using the T-SQL statement as 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 now 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 5

No let us create a partition as shown below. This partition will move any data which is older than 21 days on the first file group. Any data between 21 days old and 14 days old will stay in file group 2 and any data that is 7 days old will stay in file group 3.

use [Data Partition DB4]
GO
CREATE PARTITION FUNCTION [Data Partition Range](datetime)
AS RANGE LEFT FOR VALUES (getdate()-21,getdate()-14,getdate()-7)
Go

Note: When we execute the above statement, the getdate values are converted to actual dates, which means that we executed the statement below.

use [Data Partition DB4]
GO
CREATE PARTITION FUNCTION [Data Partition Range](datetime)
AS RANGE LEFT FOR VALUES ('2006-11-13 00:00:000', '2006-11-20  00:00:000', '2006-11-27 00:00:000')
Go

Note: 00:00:000 would actually be the time when I executed this script.

Execute the following query to see the partition information.

SELECT * FROM sys.partition_range_values

Step 6

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 groups [Data Partition DB3 FG1],

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

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

Step 7

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] (Date) )

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

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

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


Fig 1.2

Now let us insert the following rows using the T-sql statement below.

USE [Data Partition DB4]
go
insert into MyTable select 500,getdate(),100.00
insert into MyTable select 500,getdate()-1,100.00
insert into MyTable select 500,getdate()-9,100.00
go

Note: In my machine getdate() returns 2006-12-03 21:50:07.327 , select getdate()-1 returns 2006-12-02 21:50:21.437 and select getdate()-9 2006-11-24 21:50:45.467.

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

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

You can now see the result as shown below [Refer Fig 1.3]. It is obvious that the 3 new rows that we added spead across all four file groups based on the partition range, meaning any new data moved to file group 4.


Fig 1.3

Conclusion

This part of this article series illustrated partitioning based on date. In this article we saw how to partition existing data into four different parts based on certain dates.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers