Partitioning in SQL server

Partitioning in SQL server with small sample:




Summary: Table partitioning can make very large tables and indexes easier to manage, and improve the performance of appropriately filtered queries.

Table Partitioning Components:


The partition function, partition scheme, and partitioned table or index form a dependency tree, with the partition function at the top, the partition scheme depending on the partition function, and then the partitioned table or index depending on being linked to a partition scheme.

Partition function:

The partition function defines the boundary values of the initial set of partitions and the data type of the partitioned column:

o It makes no reference to any tables or disk storage.
o It forms the basis for one or more partition schemes.

create PARTITION function PF_dept(int)
AS range right FOR values (10,20,30)

Partition scheme:

The partition scheme maps particular partitions to filegroups:

o A given partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

create partition scheme ps_dept
as partition PF_dept
TO (FG1,FG2,FG3,FG4)

Partitioned table:

The partitioned table or index is tied to a particular partition scheme when it is created:

o The partition table has only an indirect relationship, through the partition scheme, to the partition function.

create table dept
(deptid int,
deptname varchar(10)
)
ON ps_dept(deptid)


CREATE TABLE EMP
(EMPID INT,
EMPNAME VARCHAR(10),
DEPT_ID INT)
ON PS_DEPT(DEPT_ID)