Insertion and Deletion by using Partition

/* 
Steps to follow
1. Add filegroup
2. Create Partition Functions
3. Create Partition Scheme
4. Create Staging, Main and Dummy table
with same structure and indexes on same parition scheme
5. Create Procedures to add, emptry, move and merge parition
6. Create Main Procedure to execute the above implemenations
7. Test Case execution
*/
 

--- * Add Filegroup in the current Database
ALTER DATABASE TEST ADD FILEGROUP FG1

-- * Create Partition Function
CREATE PARTITION FUNCTION
PFDATE (DATETIME)
AS RANGE LEFT FOR VALUES ('2008-10-25')

-- * Create Partition Scheme
CREATE PARTITION SCHEME
PSDATE AS PARTITION PFDATE TO (FG1,FG1)


-- * Create Staging Table and Index - Temporary Table that will hold the
-- data before inserting it into main and acutal table

CREATE TABLE STAGING
(ID INT IDENTITY(1,1)
 ,DATE DATETIME
) ON PSDATE(DATE)
 

CREATE CLUSTERED INDEX XXIDSTAGING ON STAGING(ID) ON PSDate(Date)

 
-- * Create Main Table and Index - Actual that you have to use to maintain data

CREATE TABLE MAIN
(ID INT NOT NULL
 ,DATE DATETIME
) ON PSDATE(DATE)

CREATE CLUSTERED INDEX XXIDMAIN ON MAIN(ID) ON PSDate(Date)

-- * Create Dummy Table and Index - This will be a dummy table to hold
-- partition data from actual table to remove

CREATE TABLE TDUMMY
(ID INT NOT NULL
 ,DATE DATETIME
) ON PSDATE(DATE)

CREATE CLUSTERED INDEX XXIDTDUMMY ON TDUMMY(ID) ON PSDate(Date)

-- * Procedure to add new or to get existing partition number on the base of value
CREATE PROCEDURE ADD_PARTITION
      (@PS_NAME VARCHAR(100)
      ,@PFILEGROUP VARCHAR(100)
      ,@PF_NAME VARCHAR(100)
      ,@CURRDATE DATETIME
      ,@TARGET_PNUM INT OUTPUT)
AS
BEGIN
DECLARE @SQL VARCHAR(200)
-- CHECK IF PARTITION EXIST
SELECT
@TARGET_PNUM=V.BOUNDARY_ID
FROM SYS.PARTITION_RANGE_VALUES V
INNER JOIN SYS.PARTITION_FUNCTIONS F 
      ON F.NAME=@PF_NAME AND F.FUNCTION_ID=V.FUNCTION_ID
WHERE V.VALUE = @CURRDATE

IF @TARGET_PNUM IS NULL
 BEGIN
      SET @SQL='ALTER PARTITION SCHEME ' + @PS_NAME + ' NEXT USED ' + @PFILEGROUP
      EXEC (@SQL)

      SET @SQL = 'ALTER PARTITION FUNCTION ' + @PF_NAME +  '() SPLIT RANGE (CAST(''' + CONVERT(VARCHAR,@CURRDATE) + ''' AS DATETIME))'
      EXEC (@SQL)
      SELECT @TARGET_PNUM=ISNULL(V.BOUNDARY_ID,0) 
      FROM SYS.PARTITION_RANGE_VALUES V
      INNER JOIN SYS.PARTITION_FUNCTIONS F 
      ON F.NAME=@PF_NAME AND F.FUNCTION_ID=V.FUNCTION_ID
      WHERE V.VALUE = @CURRDATE
 END
END

 
--- * Procedure to Remove data from acutal data's parition and to move it to dummy table
Create PROCEDURE EMPTY_PARTITION
      (@PARTITION_NUMBER INT
      ,@SOURCETABLE VARCHAR(50)
      ,@DUMMYTABLE VARCHAR(50))
AS
BEGIN
DECLARE @SQL VARCHAR(200)
SET @SQL='TRUNCATE TABLE ' + @DUMMYTABLE
EXEC (@SQL)

SET @SQL='ALTER TABLE ' + @SOURCETABLE + ' SWITCH PARTITION ' +
CONVERT(VARCHAR,@PARTITION_NUMBER) + ' TO ' + @DUMMYTABLE + ' PARTITION ' + CONVERT(VARCHAR,@PARTITION_NUMBER)

EXEC (@SQL)

END

-- * Procedure to move partition from source table to destination table
CREATE PROCEDURE MOVE_PARTITION
      (@PARTITION_NUMBER INT
      ,@SOURCETABLE VARCHAR(50)
      ,@TARGETTABLE VARCHAR(50))
AS
BEGIN
 
DECLARE @SQL VARCHAR(200)
SET @SQL='ALTER TABLE ' + @SOURCETABLE + ' SWITCH PARTITION ' + CONVERT(VARCHAR,@PARTITION_NUMBER) 
            + ' TO ' + @TARGETTABLE + ' PARTITION ' + CONVERT(VARCHAR,@PARTITION_NUMBER)
EXEC (@SQL)

END


-- * Procedure to merge all previos partitions on the base of boundary value
Create PROCEDURE MERGE_PARTITION
      (@PF_NAME VARCHAR(100)
      ,@BOUNDARY_VALUE DATETIME)
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
      SELECT @SQL=ISNULL(@SQL,'') + 'ALTER PARTITION FUNCTION ' + @PF_NAME  
         +'() MERGE RANGE (CAST(''' + CONVERT(VARCHAR,V.VALUE) + ''' AS DATETIME)); '
      FROM SYS.PARTITION_RANGE_VALUES V
      INNER JOIN SYS.PARTITION_FUNCTIONS F 
      ON F.NAME=@PF_NAME AND F.FUNCTION_ID=V.FUNCTION_ID
      WHERE V.VALUE <= @BOUNDARY_VALUE
      ORDER BY V.VALUE ASC
    EXEC (@SQL)
END

 
-- * Main procedure that will call other procedures to manage Parition.

CREATE PROCEDURE MAINDATA(@CURRDATE DATETIME = NULL)
AS
BEGIN

DECLARE @TPNUM INT
DECLARE @MERGPNUM INT

IF @CURRDATE IS NULL
      SET @CURRDATE = CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)
ELSE
      SET @CURRDATE = CAST(CONVERT(VARCHAR,@CURRDATE,101) AS DATETIME)

 
-- ADD/RETURN IF EXIST PARTITION NUMBER
-- PARTITION SCHEME, FILEGROUP, PARTITION FUNCTION, DATE, PARTITION NUMBER
EXEC ADD_PARTITION 'PSDATE','FG1','PFDATE',@CURRDATE,@TPNUM OUTPUT

 --MOVE DATA FROM MAIN TABLES PARTITION TO DUMMY TABLE
--PARTITION NUMBER, SOURCE TABLE, DUMMY TABLE
EXEC EMPTY_PARTITION @TPNUM,'MAIN', 'TDUMMY' 

 
--MOVE PARTITION FROM STAGING TO MAIN TABLE
--PARTITION NUMBER, SOURCE TABLE, TARGET TABLE
EXEC MOVE_PARTITION @TPNUM, 'STAGING', 'MAIN' 

 
--MERGE PARITION
SET @CURRDATE = @CURRDATE - 2
EXEC MERGE_PARTITION 'PFDATE', @CURRDATE

 
SELECT DATE,COUNT(*) STAGING_COUNT FROM STAGING GROUP BY DATE
SELECT DATE,COUNT(*) MAIN_COUNT FROM MAIN GROUP BY DATE
SELECT DATE,COUNT(*) DUMMY_COUNT FROM TDUMMY GROUP BY DATE

 END

 
----- ****** Testing case execution
TRUNCATE TABLE STAGING
GO
INSERT INTO STAGING VALUES('2008-10-25')
GO 100000
EXEC MAINDATA '2008-10-25'
GO
 

TRUNCATE TABLE STAGING
GO
INSERT INTO STAGING VALUES('2008-10-26')
GO 100000
EXEC MAINDATA '2008-10-26'
GO

 

TRUNCATE TABLE STAGING
GO
INSERT INTO STAGING VALUES('2008-10-27')
GO 100000
EXEC MAINDATA '2008-10-27'
GO
 
 

TRUNCATE TABLE STAGING
GO
INSERT INTO STAGING VALUES('2008-10-28')
GO 100000
EXEC MAINDATA '2008-10-28'
GO
 
----------------------- END