主页 / dba / 问题 / 220826
Accepted
DForck42
Asked: 2018-10-24 08:00:12 +0800 CST

从一种方案切换到另一种方案

  • 772

我有一个名为 dbo.messages 的表。我们期望此表中有大量数据。为了将事情保持在合理的水平，我们正在查看该表的每周分区。我想做的是每周一次，取出最旧的每周分区，然后将其关闭。我已将其设置为每周将在其自己的文件/文件组中。这本身没什么大不了的。

然后我想做的是把那个切换出来的表，把它切换成表的存档副本。归档表是否需要单独的分区函数和方案？这有什么陷阱吗？

sql-server sql-server-2016

1 个回答

  1. Best Answer

    我能够自己弄清楚。如果您从分区表切换到文件组，则可以将该表切换到具有另一个分区方案的分区表。

    这是我的概念证明：

    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
    • 0

相关问题