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 ;
No comments:
Post a Comment