step by step process to partition an existing table to a partitioned table
steps :
- create table orders
- populate test data with past 12 months to current date
- lock the table till the switch happens , create partition function
- create partition scheme
- create partition table
- create check constraints , check constraints are needed to switch the table to new partition
- switch the existing table to temp table and rename the temp to newly
partitioned table and finally drop the existing table - insert new test data
- check the rows on partition
| ------------------------------------------------------- -- Create test database ------------------------------------------------------- create database poc GO use POC ------------------------------------------------------- -- 1 Create the new Orders table. ------------------------------------------------------- CREATE TABLE Orders (iid int identity(1,1), nage int, CreatedDate datetime ) GO ------------------------------------------------------- -- 2 populate test data with past 12 months to current date ------------------------------------------------------- -- insert the orders from 2012 december to till this month insert into Orders select 10,'2012-dec-01' insert into Orders select 10,'2013-jan-01' insert into Orders select 10,'2013-jan-15' insert into Orders select 10,'2013-jan-31' insert into Orders select 10,'2013-feb-01' insert into Orders select 10,'2013-feb-15' insert into Orders select 10,'2013-feb-28' insert into Orders select 10,'2013-mar-01' insert into Orders select 10,'2013-mar-15' insert into Orders select 10,'2013-mar-31' insert into Orders select 10,'2013-apr-01' insert into Orders select 10,'2013-apr-15' ------------------------------------------------------- -- 3 create partition function -- create new table with partition. -- create partition starting next month, since you want to archive all the old data after 1 month or so ------------------------------------------------------- BEGIN TRAN select * from orders with (holdlock , tablockx) where 1=0 create partition function PF_MONTHLY_ORDERS (datetime) as RANGE RIGHT for values( '2001-01-01', -- from this date , get the minimum date -1 month '2013-05-01', -- to this date , get maxdate + 1 month to < this date it goes to 1st partition. if the current month is April(4th) '2013-06-01', -- add one month for next 12 months '2013-07-01', '2013-08-01','2013-09-01', '2013-10-01', '2013-11-01', '2013-12-01' ) ------------------------------------------------------- -- 4 create partition scheme ------------------------------------------------------- create PARTITION SCHEME PSCH_MONTHLY_ORDERS as partition PF_MONTHLY_ORDERS all to ([primary]) go ------------------------------------------------------- -- 5 create partition table -- ansi setting must be on as mentioned below ------------------------------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING OFF GO CREATE TABLE [Orders_temp] (iid int identity(1,1), nage int, CreatedDate datetime ) ON PSCH_MONTHLY_ORDERS(CreatedDate) GO SET ANSI_PADDING OFF GO ------------------------------------------------------- -- 6 create constraints on both the tables -- check constraints are needed to switch the tables , with out this its not possible to move the data ------------------------------------------------------- alter table orders add constraint chkPrtn1 check (CreatedDate >='2001-01-01' and CreatedDate is not null) alter table orders add constraint chkPrtn2 check (CreatedDate < '2013-05-01' and CreatedDate is not null) alter table orders_temp add constraint chkPrtn3 check (CreatedDate >='2001-01-01' and CreatedDate is not null) alter table orders_temp add constraint chkPrtn4 check (CreatedDate < '2020-01-01' and CreatedDate is not null) -------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- -- 7 switch the data to newly partioned table ------------------------------------------------------- alter table orders switch partition 1 to orders_temp partition 2 alter table orders_temp drop constraint chkPrtn3 alter table orders_temp drop constraint chkPrtn4 exec sp_rename orders ,orders_x exec sp_rename orders_temp ,orders select * from Orders COMMIT TRAN -- !! ROLLBACK OR COMMIT THE TRANSACTION -- !! CHECK AND DROP THE TABLE CpAuditLog_x -------------------------------------------------------------------------------------------------------------------------- -- 9 insert new test data insert into Orders select 10,'2013-apr-30' -- this will goes to 2 partition insert into Orders select 10,'2013-may -30' -- this will goes to 3 partition -------------------------------------------------------------------------------------------------------------------------- -- 10 check the rows on partitition SELECT [CreatedDate], $partition.PF_MONTHLY_ORDERS([CreatedDate]) AS 'Partition Number' FROM Orders SELECT $partition.PF_MONTHLY_ORDERS([CreatedDate]) AS 'Partition Number' , min([CreatedDate]) AS 'Min Order Date' , max([CreatedDate]) AS 'Max Order Date' , count(*) AS 'Rows In Partition' FROM Orders GROUP BY $partition.PF_MONTHLY_ORDERS([CreatedDate]) SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] ,* FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'PF_MONTHLY_ORDERS') ORDER BY [ID] ASC ------ full schema query DECLARE @TableName sysname = 'orders'; SELECT p.partition_number, fg.name, p.rows,* FROM sys.partitions p INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id WHERE p.object_id = OBJECT_ID(@TableName) --------------other meta data queries DECLARE @TableName sysname = 'orders'; SELECT distinct p.partition_number, fg.name, p.rows,sprv.value AS [Value] FROM sys.partitions p INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id INNER JOIN sys.destination_data_spaces dds ON fg.data_space_id = dds.data_space_id INNER JOIN sys.partition_schemes ps ON ps.data_space_id = dds.partition_scheme_id INNER JOIN sys.partition_functions AS spf ON spf.function_id = ps.function_id INNER JOIN sys.partition_parameters pp ON pp.function_id=spf.function_id INNER JOIN sys.partition_range_values sprv ON sprv.function_id=pp.function_id and sprv.parameter_id= pp.parameter_id WHERE p.object_id = OBJECT_ID(@TableName) and (spf.name=N'PF_MONTHLY_ORDERS') SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] ,* FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'PF_MONTHLY_ORDERS') ORDER BY [ID] ASC |
