1. What is Partition table?
Data is partitioned horizontally by range
2. How to Create Partition Table?
You can create partition table by 2 ways
- programmability
- 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
Partition Scheme: A partition scheme assigns partitions to filegroups
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
Select a column you want to use it in partition as in fig bellow
Create Partition function as in fig bellow
Create Partition Scheme as in fig bellow
Determine ranges as in fig bellow
Create Partition by select run immediately as in fig bellow
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
