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
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
No comments: