Partitioning Example Part II (Basic Example)

Below example demonstrates the below items
1. Creating Partition Function
2. Creating Partition Scheme
3. Creating a table and assigning partition to that table
4. Verifying partitions using metadata functions


CREATE DATABASE PARTITION_TEST

SELECT * FROM PARTITION_TEST..sysfiles

USE PARTITION_TEST

-- Creating a Partition Function
CREATE PARTITION FUNCTION partitionfunction1(int) AS RANGE RIGHT FOR VALUES (1000,2000,3000,4000,5000) ; -- SIX partitions

-- DROP PARTITION FUNCTION partitionfunction1  -- To drop the partition function

SELECT * FROM sys.partition_functions
/*
PARTITION 1 = UPTO 1000  
PARTITION 2 = 1001 TO 2000
PARTITION 3 = 2001 TO 3000
PARTITION 4 = 3001 TO 4000
PARTITION 5 = 4001 TO 5000
PARTITION 6 = 5000 and above

CREATE PARTITION FUNCTION partitionfunction1(int)
AS RANGE  LEFT
FOR VALUES (1000,2000,3000,4000,5000)

PARTITION 1 = UPTO 999 
PARTITION 2 = 1000 TO 1999
PARTITION 3 = 1999 TO 2999
PARTITION 4 = 2999 TO 3999
PARTITION 5 = 3999 TO 4999
PARTITION 6 = 4999 and above
*/
-- Creating a Partition Scheme
CREATE PARTITION SCHEME partitionscheme1 AS PARTITION partitionfunction1 ALL TO ('PRIMARY');

-- Create the table and defining a parition on specified column
CREATE TABLE partitioned_table (col1 int, col2 int, col3 INT ) ON partitionscheme1(col1)

insert into partitioned_table values (10,10,10)
insert into partitioned_table values (90,10,10)
insert into partitioned_table values (100,10,10)
insert into partitioned_table values (600,10,10)
insert into partitioned_table values (900,10,10)
insert into partitioned_table values (1000,10,10)
insert into partitioned_table values (2000,10,10)
insert into partitioned_table values (2500,10,10)
insert into partitioned_table values (3000,10,10)
insert into partitioned_table values (3500,10,10)
insert into partitioned_table values (4000,10,10)
insert into partitioned_table values (4500,10,10)
insert into partitioned_table values (5000,10,10)
insert into partitioned_table values (5500,10,10)
insert into partitioned_table values (6000,10,10)
insert into partitioned_table values (6500,10,10)
insert into partitioned_table values (7000,10,10)
insert into partitioned_table values (7500,10,10)
insert into partitioned_table values (8000,10,10)
insert into partitioned_table values (8500,10,10)
insert into partitioned_table values (9000,10,10)
insert into partitioned_table values (9500,10,10)
insert into partitioned_table values (9900,10,10)
insert into partitioned_table values (9901,10,10)

SELECT OBJECT_ID('partitioned_table')

SELECT * FROM sys.PARTITIONS WHERE OBJECT_ID = 2121058592

SELECT * FROM partitioned_table

--- Below command is to view data from each partition
SELECT $PARTITION.partitionfunction1(COL1) AS [PARTITION], col1 AS [DATA] FROM partitioned_table

-- Below command is to view partition boundaries and row count in each partition
SELECT $PARTITION.partitionfunction1(COL1) AS Parition_Name,
  MIN(Col1) AS Min_Value_InPartition,
  MAX(Col1) AS Max_Value_InPartition,
  COUNT(*) Partition_Record_Count
FROM partitioned_table
GROUP BY $PARTITION.partitionfunction1(Col1)