Table Partitioning

* 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.

             * 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.

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, data type is the column's type which is used to implement partitions.
  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.

 RIGHT:
 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)  

 This functions allows to create 3 partitions.
 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.

 syn:

 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.

CREATE PARTITION FUNCTION MyPartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2)  

Step2: Creating partition scheme.

CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
ALL TO ([PRIMARY])

Step3: Creating partition table

CREATE TABLE MyPartitionedTable
       (
       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')

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

Step6: Verifying the partitions

SELECT *
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.

 * 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)

 select *
 FROM sys.partitions
 WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

2. SWITCH: Swap a populated table or partition with an empty table or partition.
--------
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

--step4: Now 3rd partition becomes empty.

SELECT *
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

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.

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

ALTER PARTITION FUNCTION myRangePF1 ()
MERGE RANGE (100);