Sample Script for Partitioning - Jayanth Kurup SQL Guy

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

Please Consider Subscribing

Leave a Reply