Sample Script for Partitioning

First we create the database

CREATE DATABASE PARTITIONSAMPLE
GO
-- SET THE CONTEXT TO PARTITIONSAMPLE DATABASE
USE PARTITIONSAMPLE
GO
-- ALTER THE DATABASE TO ADD A NEW FILE GROUP , THIS ALLOWS US TO ADD ADDITIONAL DATA FILES WHICH IN TURN
-- WILL ALLOW THE DATABASE TO BE SPLIT OVER MULTIPLE DISK , REDUCNG DISK CONTENTION
-- A DISINCT FILE GROUP ALSO ALLOWS US TO SET A FILE GROUP TO READ ONLY IN CASE THE MAJORITY OF
-- THE PARTITIONED DATA IS HISTORICAL AND DOES NOT NEED TO BE MODIFIED. THIS WILL ALSO IMPROVE PERFORMANCE
ALTER DATABASE PARTITIONSAMPLE ADD FILEGROUP SECONDARY
GO
-- VERIFY THE NEW FILE GROUP IS CREATED
SELECT * FROM SYS.FILEGROUPS
GO

Then we create the file  and partition schema and function

-- MODIFIED THE DATABASE TO ADD ADDITIONAL NDF FILE. THE SCONDARY FILE GROUP WILL CONTAIN THIS FILE AND WE WILL NOW
-- BE ABLE TO STORE DATA FROM OUR TABLE ON DIFFERENT DISKS AND DIFFERENT FILES HENCE IMPROVING THE QUERY PERFORMANCE
-- ADDITIONALLY SINCE THE DATA IS SPLIT ACORSS MULTIPLE FILES WE NOW HAVE A REDUCED SUBSET OF DATA WHICH NEEDS TO BE
-- SCANNED WHEN WE WANT TO RETURN DATA USING WHERE CLAUSE.
ALTER DATABASE PARTITIONSAMPLE ADD FILE (NAME = 'SECONDARYDATAFILE' ,
FILENAME = 'INSERT PATH HERE SECONDARY.NDF')
TO FILEGROUP SECONDARY
-- VERIFY THE DATA FILES HAVE BEEN CREATED
SELECT * FROM SYS.SYSFILES
GO
-- CREATE THE PARTITION FUNCTION , THIS FUNCTION DETERMINES ON WHAT BASIS THE SPLIT OF DATA WILL OCCUR.
-- E.G IF WE WANT EVERY 100,000TH ORDER TO BE STORED IN A NEW NDF FILE THEN THE STARTING VALUE WOULD BE 100,000
-- NEXT VALUE WILL BE 200,00 , IN THE BELOW EXAMPLE I HAVE CREATED INTERVALS OF 500 , 1000 , 20000 , 60000
CREATE PARTITION FUNCTION PARTFUNC_PARTITIONTBL ( INT ) AS RANGE LEFT FOR VALUES ( 500 , 1000 , 20000 , 60000 ) ;
GO
-- CREATE THE PARITION SCHEME
-- THE FUNCTION DECIDES HOW THE DATA WILL BE SPLIT THEN THE SCEHME DECIDES WHERE IT WILL BE STORED.
-- THE ORDER OF THE FILEGROUPS DECIDE WHICH DATA GOES INTO WHICH FILE GROUP
-- THIS WILL BE CLEAR LATER ON.
-- IN THE BELOW EXAMPLE 0-500 ROWS GO TO PRIMARY FILE GROUP , 501 TO 1000 GO TO SECONDARY FILE GROUP
-- 1001 TO 20000 GOES INTO PRIMARY FILE GROUP ETC
CREATE PARTITION SCHEME PARTITIONSCHEME_1 AS PARTITION PARTFUNC_PARTITIONTBL TO ( [PRIMARY], SECONDARY,[PRIMARY], SECONDARY, SECONDARY,SECONDARY );
GO
--Partition scheme 'PARTITIONSCHEME_1' has been created successfully. 'Secondary' is marked as the next used filegroup in partition scheme 'PARTITIONSCHEME_1'.
-- CREATE TABLE USING THE ABOVE PARTITION SCHEME.
-- THIS TELLS SQL THAT FOR THE TABLE "PARTITIONTABLE" USE THE ABOVE SCHEME WHICH IN TURN USES THE FUNCTION TO SPLIT THE DATA
-- INSERTED INTO COLUMN "ID" AND PALCE THEM IN THE RESPECTIVE FILEGROUPS

Then we bind the table with the Schema

CREATE TABLE PARTITIONTABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , NAME CHAR(128)) ON PARTITIONSCHEME_1 (ID) ;
GO
-- INSERT DATA TO TEST PARTITIONING
INSERT INTO PARTITIONTABLE
SELECT NEWID()
GO 150000
-- THE FUNCTION $PARTITION. ALLOWS THE USER TO QUERY THE TABLE AND IDENTIFY WHICH PARTITION DATA GOT INSERTED INTO.
SELECT top 6000 * , $PARTITION.PARTFUNC_PARTITIONTBL (ID) FROM PARTITIONTABLE
-- DETAILS FROM WITHIN THE PARTITION
SELECT * FROM sys.partitions WHERE OBJECT_ID ('PARTITIONTABLE') = OBJECT_ID