Thursday, August 08, 2019

How to add more partitions to existing table


 --STEP 1 : Find File group

SELECT name AS AvailableFilegroups
FROM sys.filegroups WHERE type = 'FG'

--AvailableFilegroups
-------------------
--PRIMARY


--- STEP 2 : Create Partition Range Function & Schema

SELECT * FROM sys.partition_functions WHERE [name] = N'PF_CREATED_DATE'  
SELECT * FROM sys.partition_schemes   WHERE [name] = N'PS_PURCHASE_ORDER' 
  

CREATE PARTITION FUNCTION [PF_CREATED_DATE](Date) 
AS RANGE RIGHT FOR VALUES (
N'2019-08-01',
N'2019-08-02',
N'2019-08-03',
N'2019-08-04',
N'2019-08-05',
N'2019-08-06',
N'2019-08-07',
N'2019-08-08',
N'2019-08-09',
N'2019-08-10',
N'2019-08-11',
N'2019-08-12',
N'2019-08-13',
N'2019-08-14',
N'2019-08-15')

CREATE PARTITION SCHEME [PS_PURCHASE_ORDER] AS PARTITION [PF_CREATED_DATE]  TO  ( 
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ,
 [PRIMARY] ) 


--- STEP 3 : Create TABLE & Index

 SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PURCHASE_ORDER](
[PURCHASE_ORDER_ID] [bigint] NOT NULL,
[CREATED_DATE] [date]  NOT NULL,
[LOAD_ID] [int] NOT NULL
) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


CREATE CLUSTERED INDEX [CI_CREATED_DATE] ON [dbo].[PURCHASE_ORDER]
(
[CREATED_DATE] ASC,
[PURCHASE_ORDER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_PURCHASE_ORDER]([CREATED_DATE])
GO


--- STEP 4 : Add new partitions

 -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-16')

  -- Alter PS to add File group
 ALTER PARTITION SCHEME [PS_PURCHASE_ORDER]  NEXT USED  [PRIMARY]

 --ALTER  PF to add new partition
 ALTER PARTITION FUNCTION [PF_CREATED_DATE]()  SPLIT RANGE (N'2019-08-17')