Sql-server – Switching out of one scheme into another

partitioningsql serversql-server-2016

I have a table named dbo.messages. We are expecting a lot of data in this table. To keep things to a reasonable level we are looking at a weekly partitioning on that table. What I would like to do is take the oldest weekly partition, once a week, and switch it out. I have it set up that a each week will be on its own file/filegroup. This in itself isn't a big deal.

What I'd then like to do is take that switched out table, and switch it into an Archive copy of the table. Do I need a separate partition function and scheme for the archive table? Are there any gotchas with this?

Best Answer

I was able to figure it out on my own. If you switch out from a partitioned table to a file group, you can then switch that table into a partitioned table with another partition scheme.

Here's my proof of concept:

USE [master]
GO


    CREATE DATABASE [test]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'test', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
     FILEGROUP [a_fg] 
    ( NAME = N'a_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\a_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
     FILEGROUP [archive_l_null_fg] 
    ( NAME = N'archive_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
     FILEGROUP [archive_r_null_fg] 
    ( NAME = N'archive_r_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_r_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
     FILEGROUP [b_fg] 
    ( NAME = N'b_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\b_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ), 
     FILEGROUP [main_l_null_fg] 
    ( NAME = N'main_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\main_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
     LOG ON 
    ( NAME = N'test_log', FILENAME = N'T:\SQLLog01\MSSQLSERVER\LOG\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
    GO

    USE [test]
    GO




    CREATE PARTITION FUNCTION main (datetime)
        AS RANGE RIGHT FOR VALUES
        (
            '2018-1-1',
            '2018-2-1'
        )

    CREATE PARTITION FUNCTION archive (DATETIME)
        AS RANGE RIGHT FOR VALUES
        (
            '2099-01-01'
        )


    CREATE PARTITION SCHEME main_ps 
        AS PARTITION main TO
        (
            'main_l_null_fg',
            'a_fg',
            'b_fg'
        )

    CREATE PARTITION SCHEME archive_ps
        AS PARTITION archive TO
        (
            'archive_l_null_fg',
            'archive_r_null_fg'
        )


    CREATE TABLE dbo.main_table
    (
        PK_ID INT not null IDENTITY(1, 1),
        CreatedDate datetime not null
    ) ON main_ps (CreatedDate)


    CREATE TABLE dbo.archive_table
    (
        PK_ID int not null,
        CreatedDate datetime not null
    ) ON archive_ps (CreatedDate)


    INSERT INTO dbo.main_table ( CreatedDate )
    VALUES ( '2018-01-01' ),
        ( '2018-01-03' ),
        ( '2018-01-07' )


    CREATE TABLE dbo.switchout_table
    (
        PK_ID INT not null,
        CreatedDate DATETIME NOT null
    ) ON a_fg

    ALTER TABLE dbo.switchout_table
    ADD CONSTRAINT CK_SOT CHECK (CreatedDate >= '2018-01-01' AND CreatedDate < '2099-01-01')

    ALTER TABLE dbo.main_table
    SWITCH PARTITION 2 TO dbo.switchout_table

    ALTER PARTITION FUNCTION main()
        MERGE RANGE ('2018-01-01')






    ALTER PARTITION SCHEME archive_ps
    NEXT USED a_fg

    ALTER PARTITION FUNCTION archive()
        SPLIT RANGE ('2018-01-01')

    ALTER TABLE dbo.switchout_table
    SWITCH TO dbo.archive_table PARTITION 2

    DROP TABLE dbo.switchout_table
Related Question