SQL: Data partitioning in SQL Server 2005 – Part V (nice example)

Reference: http://www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005—Part-V.htm

Data partitioning in SQL Server 2005 – Part V

By Muthusamy Anantha Kumar aka The MAK

In Part IV of this article series, we have seen how to partition an existing table based on certain range of dates.

Part V illustrates how to merge and split existing partitions.

First let us create a database with a table. Then let us insert data and then partition the table.

Step 1

Let us assume we have created the folder, C:Data2, and the following subfolders. [Refer Fig 1.0]

C:Data2Primary
C:Data2FG1
C:Data2FG2
C:Data2FG3
C:Data2FG4

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 DB5′)

DROP DATABASE [Data Partition DB5]

GO

CREATE DATABASE [Data Partition DB5]

ON PRIMARY

(NAME=’Data Partition DB5′,

FILENAME=

‘C:Data2PrimaryData Partition DB5.mdf’,

SIZE=5,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG1]

(NAME = ‘Data Partition DB5 FG1’,

FILENAME =

‘C:Data2FG1Data Partition DB5 FG1.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG2]

(NAME = ‘Data Partition DB5 FG2’,

FILENAME =

‘C:Data2FG2Data Partition DB5 FG2.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG3]

(NAME = ‘Data Partition DB5 FG3’,

FILENAME =

‘C:Data2FG3Data Partition DB5 FG3.ndf’,

SIZE = 5MB,

MAXSIZE=500,

FILEGROWTH=1 ),

FILEGROUP [Data Partition DB5 FG4]

(NAME = ‘Data Partition DB5 FG4’,

FILENAME =

‘C:Data2FG4Data Partition DB5 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 DB5]

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 executing the following T-SQL statement.

USE [Data Partition DB5]

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 DB5]

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
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