PARTITIONED TABLES

FOR TABLE PARTITIONING WE HAVE TO FOLLOW SOME STEPS

1.IN THE FIRST STEP WE HAVE TO  CREATE A NEW DATABASE CALLED 'PART_DB' WITH TWO FILE GROUPS I.E PRIMARY AND SECONDARY

CREATE DATABASE PART_DB
ON PRIMARY
(NAME='PART_DB',
FILENAME=
'D:\BACKUP\PART_DB.MDF',
SIZE=50,
MAXSIZE=1000,
FILEGROWTH=50 ),
FILEGROUP SECONDARY
(NAME = 'PART_DB1',
FILENAME =
'D:\BACKUP\PART_DB1.NDF',
SIZE = 50,
MAXSIZE=1000,
FILEGROWTH=50 );

CHECKING THE FILES AND FILEGROUPS

SELECT * FROM SYS.FILEGROUPS

SP_HELPFILE

2.NOW WE HAVE TO CREATE  A PARTITION FUNCTION

CREATE PARTITION FUNCTION DB_PARTITIONRANGE (INT)
AS RANGE LEFT FOR
VALUES (5);

3.CREATE A PARRRTITION SCHEMA AND ATTACH TO FILE GROUP

CREATE PARTITION SCHEME DB_PARTITIONSCHEME
AS PARTITION DB_PARTITIONRANGE
TO ([PRIMARY], [SECONDARY]);

4.CREATE TABLE WITH PARTITION KEY AND PARTITION SCHEME

CREATE TABLE TBL_PARTITION
(P_YEAR INT NOT NULL,
DATE DATETIME)
ON DB_PARTITIONSCHEME (P_YEAR);

5.CREATE INDEX ON PARTITIONED TABLE(OPTIONAL/RECOMMENDED)

CREATE UNIQUE CLUSTERED INDEX INDX_PARTITION
ON TBL_PARTITION(P_YEAR)
ON DB_PARTITIONSCHEME (P_YEAR)

6.INSERTING VALUES INTO A TABLE

INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 1
VALUES (3,GETDATE());
INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 1
VALUES (2,GETDATE());
INSERT INTO TBL_PARTITION (P_YEAR, DATE) -- INSERTED IN PARTITION 2
VALUES (10,GETDATE());

SELECT * FROM TBL_PARTITION

SELECT *
FROM SYS.PARTITIONS
WHERE OBJECT_NAME(OBJECT_ID)='TBL_PARTITION';