About Partition Tables and Indexes

Partition Tables and Indexes –

To manage and control the large tables and indexes required to partition the table data into the different file groups.

Following object need to create for the partitioning the table and index.

-          Partition Function

-          Partition schema

CREATE PARTITION FUNCTION fnName

AS RANGE  LEFT FOR VALUE(int)

CREATE PARTITION SCHEMA scName

AS PARTITION fnName

To(Filegroup names)

CREATE TABLE (ID INT NOTNULL)

ON ScName(ColName)

CREATE UNIQUE CLUSTERED  INDEX ixName

ON TblName(ColName) ON ScName(ColName)

Steps to create the partition table –

-          Create different file group for store the large database.

-          Create Partition function and define by LEFT and RIGHT ranges.

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

CREATE PARTITION FUNCTION myRangePF2 (int)

AS RANGE RIGHT FOR VALUES (1, 100, 1000);

CREATE PARTITION FUNCTION myRangePF3 (char(20))

AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

-          Attach file groups to the Partition schema based on the partition function.

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1

TO (test1fg, test2fg, test3fg, test4fg);

CREATE PARTITION SCHEME myRangePS3

AS PARTITION myRangePF3

ALL TO ( test1fg );

-          Create table on the partition schema.

CREATE TABLE PartitionTable (col1 int, col2 char(10))

ON myRangePS1 (col1) ;

-          Create index on partition table with the schema.

CREATE NONCLUSTERED INDEX IX_PartitionTable_col1

ON PartitionTable(col1)

ON myRangePS1 (col1);

-          Alter the partition table

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );

GO

--Split the partition between boundary_values 100 and 1000

--to create two partitions between boundary_values 100 and 500

--and between boundary_values 500 and 1000.

ALTER PARTITION FUNCTION myRangePF1 ()

SPLIT RANGE (500);

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );

GO

--Merge the partitions between boundary_values 1 and 100

--and between boundary_values 100 and 1000 to create one partition

--between boundary_values 1 and 1000.

ALTER PARTITION FUNCTION myRangePF1 ()

MERGE RANGE (100);

ALTER PARTITION SCHEME MyRangePS1

NEXT USED test5fg;

-          Insert data into the partition table.

-          Retrieve data from the partition table.

SELECT $PARTITION.RangePF1 (10) ;

SELECT $PARTITION.RangePF1(Col1) AS Partition,

COUNT(*) AS [COUNT] FROM PartitionTable

GROUP BY $PARTITION.RangePF1(Col1)

ORDER BY Partition ;

SELECT * FROM PartitionTable

WHERE $PARTITION.RangePF1(Col1) = 5 ;