Partitioning Table in SQL Server: When your database has large tables. Then partitioning will be beneficialin terms of performance, scalability and
maintainability. Sql Serverpartitioning allows to spread data on various physical disks leveraging the concurrent
performance of those disks to optimize query performance.
To use partitioning we create adatabase with multiple file groups. Here is an example with three file groups.
The first is Primary Data file group. Others are Secondary file group.
Secondary data files are optional,are user-defined, and store user data. Secondary files can be used to spread
data across multiple disks by putting each file on a different disk drive.
Create database
CREATE DATABASE partioningDB 'C:\FG1\fg1.mdf',--Priamry data file 'D:\fg2\fg2.ndf',--ndf file is secondary data files make up all the data files, other thanthe primary data file
'D:\fg3\fg3.ndf',--ndf file is secondary data files make up all the data files, other thanthe primary data file
Create Partition Function
Now we need to create a partitionrange function with following syntax.
CREATE PARTITION FUNCTIONpartition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [boundary_value [ ,...n ] ] )
CREATE PARTITION FUNCTIONfunc_partition (int)
FOR VALUES (10,50) defines Boundaries.These boundaries define three partitions. The first contains all values less
than 10. The second contains values between 10 and 49. Third contains rest of
values.
Now create a partition scheme
You need to create a scheme that defines where you want topartition data. Syntax to create scheme is
CREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [,...n ] )
CREATE PARTITION SCHEME scheme_partition AS PARTITIONfunc_partition
Partition is created on primary and fg2, and fg3 file groups. Now to partitioning a table you
need to create a table or alter table
Table Creation with partition
CREATE TABLE Person (fname nvarchar(40), lname nvarchar(40), [uid] int) ON scheme_partition ([uid]) Partitioning on Existing Table
ALTER TABLE Person ADD CONSTRAINT [PK_uid] PRIMARYKEY CLUSTERED
( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ON scheme_partition([uid]) Now partitioning of table has been done. To test insertsome data in table
insert into Person values ('A','a',1) insert into Person values ('B','b',2) insert into Person values ('C','c',3) insert into Person values ('D','d',23) insert into Person values ('E','e',24) insert into Person values ('F','f',25) insert into Person values ('G','g',10) insert into Person values ('H','h',60) insert into Person values ('I','i',58) insert into Person values ('J','j',54) insert into Person values ('K','k',55) Now to verify your data run this query
WHERE OBJECT_NAME(OBJECT_ID)='Person';
See first partition have 3 rows means UID<10. As (1, 2,and 3)
Then Second contains 4 rows UID<50. (23, 24, 25, 10) Then Third contains 4 rows UID>=50 as (55, 54, 58, 60) 
