0

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?

2
  • Do you want the archive copy of the table on the same filegroup(s) as the main table? Also, I would recommend working through some examples like this. One big gotcha related to partitioning is figuring out where each command is going to move data, and how much data it's going to move. – mathewb Oct 23 '18 at 16:44
  • @mathewb i forgot to mention that i have each partition mapped to a file/file group. updated my question with the info. – DForck42 Oct 23 '18 at 18:01
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.