sql server partition table online from non partition table

step by step process to partition an existing table to a partitioned table



steps :

1. create table orders
2. populate test data with past 12 months to current date
3. lock the table till the switch happens , create partition function
4. create partition scheme
5. create partition table
6. create check constraints , check constraints are needed to switch the table to new partition
7. switch the existing table to temp table and rename the temp to newly
   partitioned table and finally drop the existing table
8. insert new test data
9. 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