Partitioning Example to split, merge and switch options

In our previous example we have defined below partition function and scheme as below

CREATE PARTITION FUNCTION SalesData_Function (DATETIME)  AS RANGE LEFT FOR VALUES ('12/31/2010','12/31/2011','12/31/2012')

CREATE PARTITION SCHEME SalesData_Scheme AS PARTITION SalesData_Function TO (Sales_2010, Sales_2011, Sales_2012, [PRIMARY])

According to above partition scheme if you insert a record of 2013 year it will insert in Primary partition.  If you want to insert 2013 records in a new partition with file group then
we need to Split Partition, Below is the example :
--- Adding a new filegroup
ALTER DATABASE SalesDB ADD FILEGROUP Sales_2013

--- Adding a physical file to filegroup
ALTER DATABASE SalesDB ADD FILE (NAME = 'Sales2013', FILENAME= 'C:\Sales2013.ndf', SIZE =1000KB, MAXSIZE=UNLIMITED, FILEGROWTH =5%) TO FILEGROUP Sales_2013

--- Modify partition scheme to make use of new filegroup
ALTER PARTITION SCHEME SalesData_Scheme NEXT USED Sales_2013

-- Modify parition function to split the data range
ALTER PARTITION FUNCTION SalesData_Function() SPLIT RANGE('12/31/2013')

The above partition function splits the data of specified range and moves into new file group, in our case it will move all the 2013 records into Sales_2013 filegroup.

--- Verify whether records are moved into new filegroup are not
insert into Sales_Data values(1015,'order15','Bangalore','12-Mar-2013', 100)
insert into Sales_Data values(1016,'order16','Chennai','23-Apr-2013', 100)

SELECT DISTINCT OBJECT_NAME(si.object_id) Table_Name, p.partition_number,fg.name,
p.rows, (SELECT value FROM sys.partition_range_values prv WHERE prv.boundary_id = p.partition_number) Partition_Value
 FROM sys.partitions p, sys.indexes si, sys.partition_schemes ps,
sys.destination_data_spaces dds, sys.filegroups fg, sys.partition_functions pf
 WHERE p.object_id = si.object_id and p.index_id = si.index_id AND si.data_space_id = ps.data_space_id AND dds.partition_scheme_id = ps.data_space_id
AND fg.data_space_id = dds.data_space_id AND p.partition_number = dds.destination_id AND pf.function_id = ps.function_id

Merge Partition -- After couple of years we decided we need to keep last three year records in three different filegroups and need to merge rest of the data into single partition.
That functionality can be done using merge partition.

In our Sales_data table we have 4 years of data and 5 partitions including primary. we will merge first two years ie., 2010, 2011 into one partition.

-- Verify number of records in each year
SELECT YEAR(order_date), COUNT(*) AS "OrderYear" FROM sales_data GROUP BY YEAR(order_date)

-- Modifying partition function to merge 2009, 2010 records into next available partition.
ALTER PARTITION FUNCTION SalesData_Function() MERGE RANGE ('12/31/2010') -- It merges all 2009, 2010 records into Sales_2011 partition and count of Sales_2011FG records will get increased

Note : Merge will not remove physical filegroup from the database structure, it just do merging of two different sets into one.

-- To verify 2009, 2010 records are in which partition
SELECT $Partition.SalesData_Function(Order_Date) AS [Partition],* from Sales_Data ORDER BY [Partition] -- Verify date by partitioned column

Partition Switching -- Switching of partition helps to archive historical data in a faster way because it is a metadata operation
Note : Switch partition has certain limitations
a) It needs an empty table with same schema as original table.
b) source and target table must share same filegroup to swith partition

1. Create a temporary table for archival process (same as original schema)
2. Invoke alter partition switch command to switch the partition
3. Insert data into permanent archive table
4. drop the temporary table

--- Creating a temporary table for parition switching
SELECT * INTO sales_data_temp FROM sales_data WHERE YEAR(order_date)=2000

ALTER TABLE sales_data SWITCH PARTITION 1 TO sales_data_temp
The above partition switch will throw the below error

ALTER TABLE SWITCH statement failed. table 'SalesDB.dbo.sales_data_temp' is in filegroup 'PRIMARY' and partition 1 of table 'SalesDB.dbo.sales_data' is in filegroup 'Sales_2012'.
To do partition switching both tables must share same filegroup

DROP TABLE sales_data_temp
CREATE TABLE [dbo].[Sales_Data_temp](
 [Order_ID] [int] NULL,
 [OrderName] [varchar](15) NULL,
 [Order_City] [varchar](30) NULL,
 [Order_Date] [datetime] NULL,
 [Order_Amt] [int] NULL
) ON Sales_2012
GO

ALTER TABLE sales_data SWITCH PARTITION 1 TO sales_data_temp -- This will switch partition 1 into newly created temporary table and removes records from original table.

SELECT * FROM sales_data_temp
SELECT * FROM sales_data

Partitioning Example Part III (With Filegroups)

--- Creating a demo database with filegroups to store associated data on respective filegroups
CREATE DATABASE SalesDB ON PRIMARY
( NAME = Primary_Data, FILENAME = 'C:\Sales_Data.mdf',
 SIZE = 100, MAXSIZE = 150, FILEGROWTH = 5 ),
 FileGroup Sales_2010 ( NAME = Sales2010, FILENAME = 'D:\Sales2010.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2011   ( NAME = Sales2011,   FILENAME = 'E:\Sales2011.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ),
 FileGroup Sales_2012 ( NAME = Sales2012, FILENAME = 'F:\Sales2012.ndf',
    SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

--- Verify file groups are created properly
USE SalesDB
SELECT * FROM SalesDB..sysfiles

--- Creating Partition function to store 3 years of data on it
CREATE PARTITION FUNCTION SalesData_Function (DATETIME)
AS RANGE LEFT FOR VALUES ('12/31/2010','12/31/2011','12/31/2012')  -- FOUR PARTITIONS

--- Creating a partition scheme to store data on respective filegroups
CREATE PARTITION SCHEME SalesData_Scheme AS PARTITION SalesData_Function TO (Sales_2010, Sales_2011, Sales_2012, [PRIMARY])

-- Creating a sales demo partitioned table on order_date column
Create table Sales_Data (Order_ID int, OrderName varchar(15), Order_City varchar(30), Order_Date DATETIME, Order_Amt INT) ON SalesData_Scheme(Order_Date)

--- Insert demo data
insert into Sales_Data values(1015,'order01','Bangalore','20-Jul-2009', 100)
insert into Sales_Data values(1001,'order1','Hyderabad','12-Jan-2010', 100)
insert into Sales_Data values(1002,'order2','Chennai','06-Sep-2010', 100)
insert into Sales_Data values(1003,'order3','Bangalore','11-Nov-2010', 100)
insert into Sales_Data values(1004,'order4','Hyderabad','11-Feb-2011', 100)
insert into Sales_Data values(1005,'order5','Chennai','04-Mar-2011', 100)
insert into Sales_Data values(1006,'order6','Bangalore','09-Jul-2011', 100)
insert into Sales_Data values(1008,'order7','Chennai','15-Aug-2011', 100)
insert into Sales_Data values(1009,'order8','Hyderabad','20-Dec-2011', 100)
insert into Sales_Data values(1010,'order9','Bangalore','19-Jan-2012', 100)
insert into Sales_Data values(1011,'order10','Bangalore','14-Apr-2012', 100)
insert into Sales_Data values(1012,'order11','Hyderabad','24-Dec-2012', 100)
insert into Sales_Data values(1013,'order12','Chennai','1-Jan-2013', 100)
insert into Sales_Data values(1014,'order13','Hyderabad','09-Jun-2013', 100)
insert into Sales_Data values(1015,'order14','Bangalore','20-Jul-2012', 100)

SELECT * FROM Sales_Data  -- Verify records

SELECT $Partition.SalesData_Function(Order_Date) AS [Partition],* from Sales_Data ORDER BY [Partition] -- Verify date by partitioned column

--- Verify number of rows on each partitions along with filegroup name and partition max value
SELECT DISTINCT OBJECT_NAME(si.object_id) Table_Name, p.partition_number,fg.name,
p.rows, (SELECT value FROM sys.partition_range_values prv WHERE prv.boundary_id = p.partition_number) Partition_Value
FROM sys.partitions p, sys.indexes si, sys.partition_schemes ps,
sys.destination_data_spaces dds, sys.filegroups fg, sys.partition_functions pf
WHERE p.object_id = si.object_id and p.index_id = si.index_id AND si.data_space_id = ps.data_space_id AND dds.partition_scheme_id = ps.data_space_id
AND fg.data_space_id = dds.data_space_id AND p.partition_number = dds.destination_id AND pf.function_id = ps.function_id
 

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)
 

Table Partitioning Basics - Part I

Basics of Table Partitions

Partitioning is the way to segregate your data into subsets. The main reason for doing this is for query
performance. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required instead of whole table. 

Benefits of Partitioning
Manageability
Query Performance
Data Archival
Partitioned objects will increase the performance of Select, Insert, Update and Delete queries.


Note : This feature is available only in Enterprise Edition
Steps to Partitioning SQL Server Table
1. Create Partition Function
2. Create Partition Scheme
3. Create partition Table

Step 1 -- Partition Function - The partition function defines how you want to partition of Data. This partition function is not dependent on any table, we are generically defining a technique for splitting data. We need to define partitions by specifying the boundaries, for example we have a sales table that contains information of sales data for last 3 years. We can partition that table into three partitions using the following function.

Create partition function PartitionByYear (datetime) as range left for values
('2011-01-01T00:00:00', '2012-01-01T00:00:00', '2013-01-01T00:00:00')

If I used “RANGE RIGHT” the first partition include all values less than 2011, and second contains values between 2011 and 2012 and third values between 2012 and 2013. Range Left will have include values less than or equal to 31-12-2011, the second partition starts from 1-1-2012 onwards and third from 1-1-2013 onwards.

Step 2 – Partition Scheme Creation – After defining partition function we need to create a partition
scheme defining where you want to partition it. This scheme directly links with partitions to filegroups. If we have 3 filegroups (fg1, fg2, fg3 then below is the syntax to create partition scheme).

Create Partition Scheme Sales_PartScheme as partition partitionByYear to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

If you observe the above example that partition scheme is also an independent object still we haven’t linked it to any specific table in the database, which means this is a reusable object where we can use this partition scheme on any number of database tables.

Step 3 – Partitioning a Table -- After creating partition scheme now we are ready to create a partitioned table. It is a straight forward process where we need to add partition scheme name to ON clause at the time of table creation.
For Example
Create table sales (sales_id bigint, item_name varchar(30), item_desc varchar(200), item_make varchar(100), sale_date datetime) on Sales_PartScheme(sale_date);

Below views used to view partitions
sys.partitions, sys.partition_functions, sys.partition_schemes