Table Partitioning in SQL Server 2005

--Creatinmg Partition Function
CREATE PARTITION FUNCTION PartiFunc( INT )
AS RANGE LEFT FOR VALUES
(1, 10, 100, 1000, 10000, 100000, 1000000 )

--Creatinmg Partition Scheme
CREATE PARTITION SCHEME PartiSche
AS PARTITION PartiFunc ALL TO ([PRIMARY] )

--Creatinmg Partition Table
CREATE TABLE dbo.PartitionTable
(RowIDINTIDENTITY(1, 1),
NumberINT
)ON PartiSche ( RowID )

INSERTdbo.PartitionTable
(Number )
SELECTROW_NUMBER() OVER( ORDER BY [name] ) AS RID
FROMsys.all_objects

--View of partiton Recrds 
SELECTCONVERT( VARCHAR(16), PS.[name] )AS Partition_Scheme, P.partition_number,
CONVERT( VARCHAR(10), DS2.[name])AS PartionFileGroup,
CONVERT( VARCHAR(19), COALESCE( V.[Value], '' )) AS Range_Boundry,
STR( P.Rows, 9 ) AS Rows
FROMsys.Indexes I
INNER JOINsys.partition_schemesPSONI.data_space_id= PS.data_space_id
INNER JOINsys.destination_data_spaces DDSONPS.data_space_id= DDS.partition_scheme_id
INNER JOINsys.data_spacesDS2ONDDS.data_space_id= DS2.data_space_id
INNER JOINsys.partitionsPONDDS.destination_id= P.partition_number AND
P.[Object_id]= I.[Object_ID] AND
P.index_id= I.index_id
INNER JOINsys.partition_functionsPFONPS.function_id= PF.function_id
LEFT  JOINsys.partition_range_valuesVONPF.function_id= V.function_id AND
V.boundary_id= P.partition_number - pf.boundary_value_on_right
WHEREi.[object_id]= OBJECT_ID( 'PartitionTable' ) AND
i.index_id= 0
ORDER BY P.partition_number

CREATE PARTITION FUNCTION myPartFn ( NUMERIC (18,2) ) AS RANGE LEFT FOR VALUES( 5000, 10000, 15000, 20000, 25000 )
/* <=5000, >5000 AND <=10000, >10000 AND <=15000, >15000 AND <=20000, >20000 AND <=25000, >25000 */
--CREATE PARTITION FUNCTION myPartFn ( NUMERIC (18,2) ) AS RANGE RIGHT FOR VALUES( 5000, 10000, 15000, 20000, 25000 )
/* <5000, >=5000 AND <10000, >=10000 AND <15000, >=15000 AND <20000, >=20000 AND <25000, >=25000 */
CREATE PARTITION SCHEME myPartSchm AS PARTITION myPartFn ALL TO ([PRIMARY])

SELECT * FROM sys.partition_functions; SELECT * FROM sys.partition_schemes;

CREATE TABLE dbo.myPartitionTable
( EmployeeIDINTIDENTITY(1,1), SalaryAmtNUMERIC(18, 2) ) ON myPartSchm( SalaryAmt )

CREATE CLUSTERED INDEX C_INDX_myPartitionTable_00001 ON dbo.myPartitionTable( EmployeeID ) 
CREATE NONCLUSTERED INDEX NC_INDX_myPartitionTable_00001 ON dbo.myPartitionTable( SalaryAmt ) 

SELECT * FROM sys.partitions WHERE [object_id] = OBJECT_ID(N'dbo.myPartitionTable')

INSERT INTO dbo.myPartitionTable( SalaryAmt )
SELECT 2000 UNION ALL SELECT 2500 UNION ALL SELECT 3000 UNION ALL SELECT 3500 UNION ALL SELECT 5000 UNION ALL 
SELECT 6000 UNION ALL SELECT 7300 UNION ALL SELECT 7500 UNION ALL SELECT 9800 UNION ALL SELECT 8900 UNION ALL 
SELECT 11000 UNION ALL SELECT 7890 UNION ALL SELECT 15200 UNION ALL SELECT 17500 UNION ALL SELECT 2500 UNION ALL 
SELECT 25000 UNION ALL SELECT 78000 UNION ALL SELECT 65000 UNION ALL SELECT 22000 UNION ALL SELECT 20000

SELECT * FROM dbo.myPartitionTable WHERE SalaryAmt <= 5000

SELECT * FROM sys.partitions WHERE [object_id] = OBJECT_ID(N'dbo.myPartitionTable')

DROP TABLE dbo.myPartitionTable
DROP PARTITION SCHEME myPartSchm 
DROP PARTITION FUNCTION myPartFn