Database Expert's Table Partitioning on larger sized tables.

Table Partitioning on Larger sized Tables OLTP Database

There is need when database tables grown very large.

Click here to know more,Table Partitioning

Four thing to be done before partitioning.

1.Database with Multiple FileGroup.

2.Create partition Function.

3.Create Partition Scheme.

3.Create partition.

Now,We follow above four steps to make table as partitioned.

1. Create FileGroup for Partition .

USE [master]
GO
ALTER DATABASE [DatabaseName] ADD FILEGROUP [FG01]
GO
ALTER DATABASE [DatabaseName] ADD FILEGROUP [FG02]
GO
ALTER DATABASE [DatabaseName] ADD FILEGROUP [FG03]
GO
ALTER DATABASE [DatabaseName] ADD FILEGROUP [FG04]
GO


Add Files in Each file group.Assign Different file path.




USE [master]
GO
ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'FG01', FILENAME = N'C:\FG01\FG01.ndf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG01]
GO
ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'FG02', FILENAME = N'C:\FG02\FG02.ndf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG02]
GO
ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'FG03', FILENAME = N'D:\FG03\FG03.ndf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG03]
GO
ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'FG04', FILENAME = N'E:\FG04\FG04.ndf' , SIZE = 1048576KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG04]
GO
 2.Create Partition function on DATETIME Column.

CREATE PARTITION FUNCTION pf_TableName_ColumnName (DateTime)
 AS RANGE RIGHT
 FOR VALUES ('20100620','20110630','20120630','20121231')


3.Create Partition Scheme based on Partition Function created on step 3.


 CREATE PARTITION SCHEME ps_TableName_ColumnName
 AS PARTITION pf_TableName_ColumnName
 TO ([primary],[FG01], [FG02], [FG03], [FG04])

4.Create Partition.


GO TO SSMS-->Database Name-->Expand it -->Tables-->TableName(For Partittion)-->Right Click on it.-->Storage-->Create Partition.







Select Column based on that create partition




Click Next...

Existing Partition function available when we created on Step 2.



 Click Next...


Existing Partition Scheme available when we created on Step 3.




Click Next...





Run Immediate...



 



Click Finish and wait for complete it.It will take time depends on number of rows.