Wednesday, September 19, 2012

Partitioning existing table SQL2008R2

Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.

Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index  on different drive NCINDEX_FG5

USE [TestDB]
GO
--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}


--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION  PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])

--Step 3. CREATE CLUSTERED INDEX 1
-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
      [M_ID] ASC
) ON SvrIdScheme(s_id)

-- Step 4.  CREATE NONCLUSTERED INDEX 2 on Separate file group  
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(  [t_id] ASC,  [s_id] ASC
)ON [NCINDEX_FG5] 
GO

--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
 GO

No comments: