* It is a new feature introduced in SQL Server 2005.
* While working with large tables, generally we face the following problems
* Less performance.
* Pressure increases on the hardware where the table is created.
* Index size also increases.
* We have to rebuild or reorganize the complete index always.
select * from mynewpartition
ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24
GO
3. MERGE: Combine two adjacent partitions into a single partition.
---------------------------------------------------------------
* While working with large tables, generally we face the following problems
* Less performance.
* Pressure increases on the hardware where the table is created.
* Index size also increases.
* We have to rebuild or reorganize the complete index always.
* To overcome all these problems we can divide the table horizontally into more than on part.
This process is called table partitioning.
* Dividing the table into segments horizontally is table partitioning.
* Each part is called partition. All the partitions consists of same structure.
* Each partition can be stored in different files/filegroups.
* One of a performance improvement technique.
* We can split the table into parts depending on some column values.
* Dividing the table into segments horizontally is table partitioning.
* Each part is called partition. All the partitions consists of same structure.
* Each partition can be stored in different files/filegroups.
* One of a performance improvement technique.
* We can split the table into parts depending on some column values.
Steps
1) Define a partition function
* Here we will mention the ranges of partitions.
syn:
CREATE PARTITION FUNCTION <function_Name> (data type)
AS RANGE LEFT/RIGHT FOR VALUES (V1,V2,....)
* Here we will mention the ranges of partitions.
syn:
CREATE PARTITION FUNCTION <function_Name> (data type)
AS RANGE LEFT/RIGHT FOR VALUES (V1,V2,....)
* Here, data type is the column's type which is used to implement partitions.
i.e. partition key data type.
i.e. partition key data type.
LEFT:
Specifies that partition values will be less than or equal to the values defined in the Partition Function.
Specifies that partition values will be less than or equal to the values defined in the Partition Function.
RIGHT:
Specifies that partition values will be less than the values defined in the Partition Function.
Specifies that partition values will be less than the values defined in the Partition Function.
EX:
CREATE PARTITION FUNCTION MyPartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2)
AS RANGE LEFT FOR VALUES (1,2)
This functions allows to create 3 partitions.
First partition consists of values <=1
Sec >1 and <=2
Third >2
First partition consists of values <=1
Sec >1 and <=2
Third >2
2) Define a partition scheme from the partition function
* It defines the locations for partitions. i.e. which partition should be maintain in which file group.
* We can maintain each partition in a seperate filegroup or all partitions in a single file group.
* It defines the locations for partitions. i.e. which partition should be maintain in which file group.
* We can maintain each partition in a seperate filegroup or all partitions in a single file group.
syn:
CREATE PARTITION SCHEME <name> AS
PARTITION <partition_functionname>
ALL TO (fg1,fg2,.........)
CREATE PARTITION SCHEME <name> AS
PARTITION <partition_functionname>
ALL TO (fg1,fg2,.........)
3) Create the partition Table
CREATE TABLE <tname>
(
colname type,
PartCol type
)
ON
<partition_schemename> (PartCol)
EX:
Step1: Creating partition Function.
(
colname type,
PartCol type
)
ON
<partition_schemename> (PartCol)
EX:
Step1: Creating partition Function.
CREATE PARTITION FUNCTION MyPartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2)
AS RANGE LEFT FOR VALUES (1,2)
Step2: Creating partition scheme.
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
ALL TO ([PRIMARY])
PARTITION MyPartitionRange
ALL TO ([PRIMARY])
Step3: Creating partition table
CREATE TABLE MyPartitionedTable
(
i INT ,
s CHAR(8000) ,
PartCol INT
)
ON
MyPartitionScheme (PartCol)
(
i INT ,
s CHAR(8000) ,
PartCol INT
)
ON
MyPartitionScheme (PartCol)
Step4: To display partition details
SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
Step5: Inserting sample rows
INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, 'a', 1
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4
Step6: Verifying the partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
Step7: To find the given value falls in which partition
SELECT PartitionNo = $partition.MyPartitionRange(6)
What Operations Can Be Performed on Partitioned Data
---------------------------------------------------------------------
1. SPLIT: Insert a boundary in an existing partition to create a new partition.
---------------------------------------------------------------------
1. SPLIT: Insert a boundary in an existing partition to create a new partition.
* If the size of a partition becomes very large then we can split it into two partitions.
syn:
ALTER PARTITION FUNCTION <function_name>() split RANGE (value)
Ex:
ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)
syn:
ALTER PARTITION FUNCTION <function_name>() split RANGE (value)
Ex:
ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)
select *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
2. SWITCH: Swap a populated table or partition with an empty table or partition.
--------
Step1:
--------
Step1:
select * into MyNewPartition from MyPartitionedTable where 1=3
--copying rows present in 3rd partition into MyNewPartition
ALTER TABLE MyPartitionedTable switch PARTITION 3 TO MyNewPartition
--STEP3:
select * from MyNewPartition
select * from MyNewPartition
--step4: Now 3rd partition becomes empty.
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')
--Copying Rows present in a table into required partion
Step1:
select * into MyNewPartition from MyPartitionedTable where 1=3
Step2: Copy rows present in non-partitioned table into 4th partition
Step2: Copy rows present in non-partitioned table into 4th partition
ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 4
Note1:
* MyNewPartition should consists of check constraint which allows the respective partition range.
* It should be there on same filegroup where the respective partition is present.
* MyNewPartition should consists of check constraint which allows the respective partition range.
* It should be there on same filegroup where the respective partition is present.
select * from mynewpartition
ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24
GO
3. MERGE: Combine two adjacent partitions into a single partition.
---------------------------------------------------------------
* If the no of rows in the partitions are less then if we want then we can merge into single partition.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
ALTER PARTITION FUNCTION myRangePF1 ()
MERGE RANGE (100);
MERGE RANGE (100);
