Partition Table in SQL Server 2008

1. What is Partition table?

Data is partitioned horizontally by range

Partition Table

2. How to Create Partition Table?

You can create partition table by 2 ways

  1. programmability
  2. wizard in SQL Server 2008 New

But you must understand 2 things before creation Partition Tables

Partition Function: Partition functions define partition boundaries and Boundary values can be assigned to LEFT or RIGHT

clip_image004

Partition Scheme: A partition scheme assigns partitions to filegroups

clip_image006

3. How to Create Partition Table by wizard in SQL Server 2008?

Right click on table then go to storage then select Create Partition as in fig bellow

1

Select a column you want to use it in partition as in fig bellow

2

Create Partition function as in fig bellow

3

Create Partition Scheme as in fig bellow

4

Determine ranges as in fig bellow

5

Create Partition by select run immediately as in fig bellow

6    

Congratulation your table now partitioned

4. How to Create Partition Table programmability?

/***************************************************

1)Create database TestDb

****************************************************/

CREATE DATABASE [TestDb] ON PRIMARY

( NAME = N’TestDb’, FILENAME = N’C:\db\TestDb.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg1]

( NAME = N’f1′, FILENAME = N’C:\db\f1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg2]

( NAME = N’f2′, FILENAME = N’C:\db\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg3]

( NAME = N’f3′, FILENAME = N’C:\db\f3.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg4]

( NAME = N’f4′, FILENAME = N’C:\db\f4.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N’TestDb_log’, FILENAME = N’C:\db\TestDb_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

USE [TestDb]

GO

/***************************************************

3)Create Partition Function

****************************************************/

Create Partition Function pf_Date(Date)

as range right

For values(’01/01/2006′,’01/01/2007′,’01/01/2008′)

go

/***************************************************

3)Create Partition scheme

****************************************************/

Create Partition Scheme ps_Date

AS Partition pf_Date

to(fg1,fg2,fg3,fg4)

go

/***************************************************

3)Create Partition Contact Table

****************************************************/

CREATE TABLE [dbo].[Contact](

[ContactId] [int] IDENTITY(1,1) NOT NULL,

[FullName] [nvarchar](50) NULL,

[CreationDate] [date] NULL

)on ps_Date(CreationDate)

GO

/***************************************************

4)Insert rows to table

****************************************************/

insert into dbo.Contact values(‘amosa’,’01/01/2006′)

insert into dbo.Contact values(‘mmosa’,’01/01/2007′)

insert into dbo.Contact values(‘zemo’,’01/01/2008′)

insert into dbo.Contact values(‘test1′,’05/01/2005’)

insert into dbo.Contact values(‘test2′,’05/01/2006’)

insert into dbo.Contact values(‘test3′,’05/01/2007’)

insert into dbo.Contact values(‘test4′,’05/01/2008’)

insert into dbo.Contact values(‘test5′,’05/01/2009’)

go

/***************************************************

5)Query Table to know where did rows save

****************************************************/

Select *,$partition.pf_Date(CreationDate)FileGroupNo From dbo.Contact

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: