Home > SQLServer 2005 Partitioning > How to create partitioned table and moving non-partitioned tables to partition schemes

How to create partitioned table and moving non-partitioned tables to partition schemes

 Tables Partitioning is a new feature available in SQL Server 2005, focusing on 

improving performance of large database systems and Highly Transactional Databases. Partitioning feature is only available with SQLServer2005 EE and DE.

This article focuses on how to create a partitioned table and moving non-partitioned tables to partition schemes.

Steps: Creating the Partitioned Tables

1.Add FileGroups

 

USE [Database name]

GO

ALTER DATABASE [AdventureWorks] ADD FILEGROUP[FG_od]

GO

 

 

2.Add Secondry Data Files (.ndf) and associate it with respective filegroups

 

USE [Database name]

GO

ALTER DATABASE [AdventureWorks] ADD FILE

(

NAME = N’01_31_Jan_Dec_2008′,

FILENAME = N’G:\RSDBSecondryDataFiles1_31_Jan_Dec_2008.ndf’ ,SIZE = 2048KB ,FILEGROWTH = 1024KB

)TO FILEGROUP [FG_01]

GO

 

Once the fileGroups and secondrydatafiles have been added,Create partition fucntion and partition schemes.

3.Create Partition Function (creating partition function on datatype Int.)

 

 

USE [Database name]
GO

CREATE PARTITION FUNCTION [PF_DBAW_ON_IDNUMBER] ( INT )

AS RANGE

LEFT FOR VALUES (130000000,260000000,520000000,1040000000,…..)

 

     Creating partition fucntion on DateTime datatype.

 

USE [Database name]
GO

CREATE PARTITION FUNCTION [PF_DBAW_ON_DateTime](DateTime)

AS RANGE

LEFT FOR VALUES

(‘20080331 23:59:59:997’,–Jan-Mar2008

‘20080630 23:59:59:997’,–Apr-Jun2008

‘20080930 23:59:59:997’,–July-Sept2008

‘20081231 23:59:59:997’,–Oct-Dec2008

‘20090331 23:59:59:997’—Jan-Mar2009

)

RANGE LEFT/RANGE RIGHT

This is probably one of the more confusing aspects of table partitioning. Partition functions are defined with either RANGE LEFT or RANGE RIGHT. One way to remember the difference is that a function with RANGE LEFT means that the partition data relative to the boundary is to the left of the boundary; RANGE RIGHT means that the partition data relative to the boundary is to the right of the boundary.

If the boundary is <= 130000000 the value will be written in First filegroup.

If the boundry is > 130000000 and <= 260000000 the value will written in second filegroup and so on.

 

4. Create Partition Schemes

 

USE [Database name]
GO

CREATE PARTITION SCHEME PS_DBAW —name of partition scheme

AS PARTITION [PF_DBAW_ON_IDNumber]

TO (FG_01, FG_02, FG_03,FG_04,[Primary])

GO

— all partition are held as per the filegroup series and depending upon function.)

 

5. Create Table using partition Schemes (Partitioned Table)

 

Create table TestPartition

(

Cust_id INT,

Cust_date datetime

)

ON [PS_DBAW] ([Cust_id])

 

     Check the table properties to confirm table is partitioned or not.

     run below command to check data location.

     exec sp_help TestPartition

     Data_located_on_filegroup

     —————————

     PS_DBAW —————Partition Scheme

 

     Check Table partition structure

SELECT *

FROM sys.partitions

WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)

 Query Output:-  

partition_id

Object_id

index_id

partition_number

hobt_id

rows

72057594058964992

919674324

0

1

72057594058964992

0

72057594059030528

919674324

0

2

72057594059030528

0

72057594059096064

919674324

0

3

72057594059096064

0

72057594059161600

919674324

0

4

72057594059161600

0

72057594059227136

919674324

0

5

72057594059227136

0

 

Lets insert some data and see how it get partitioned. 

Declare @int int

set @int=0

while @int<10000

Begin

set @int=@int+14

BEGIN Tran

insert into dbo.TestPartition(Cust_id,Cust_date) values(@int,getdate())

commit

End

 Check Table partition structure

SELECT *

FROM sys.partitions

WHERE OBJECT_ID = OBJECT_ID(‘TestPartition’)

Expected Output:-

partition_id

Object_id

index_id

partition_number

hobt_id

rows

72057594058964992

919674324

0

1

72057594058964992

4501

72057594059030528

919674324

0

2

72057594059030528

3658

72057594059096064

919674324

0

3

72057594059096064

578

72057594059161600

919674324

0

4

72057594059161600

0

72057594059227136

919674324

0

5

72057594059227136

0

 

 

Check data in each filegroup.

SELECT $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)

AS [Partition Number]

, min(o.Cust_id) AS [Min Cust_id No]

, max(o.Cust_id) AS [Max Cust_id No]

, count(*) AS [Rows In Partition]

FROM TestPartition AS o

GROUP BY $partition.PF_DBAW_ON_IDNUMBER(o.Cust_id)

ORDER BY [Partition Number]

 

Steps : Moving non-partitioned tables to Partitioned schemes.

Before moving non-partitioned table to partition scheme , script all relationship(FK).Once you drop the clustered index participating in FK relationship all relationship will be dropped.

  1. Run sp_help tablename command to check the table status.Do not forget to check Foregin Key relationship.

  2. Drop clustered index and recreate again using dataspace type as partiton scheme.

 

GO

DROP INDEX <table_name>.<index_name>

GO

Create Clustered index

BEGIN TRANSACTION

GO

CREATE CLUSTERED INDEX IX_Cust_Id ON TestPartition

(

Cust_id

) WITH( STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON PS_DBAW(Cust_id)

GO

COMMIT

 

Once clustered Index is created , check the properties of the table,It will show table is partitioned.

Now create all the relationship again if any.

 

 

 

 

 

 

 

 

  1. No comments yet.
  1. No trackbacks yet.

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: