主页 / dba / 问题 / 143051
Accepted
Kenneth Fisher
Asked: 2016-07-06 14:09:36 +0800 CST

分区切换脚本的问题

  • 772

使用一些动态 SQL 来创建一个 13 个月的滚动分区表。 请参阅此处了解一些背景信息。

动态SQL部分很简单，第一个分区切换出来没有问题。不幸的是，当我到达第二个分区时，我开始收到此错误：

Msg 4972, Level 16, State 1, Line 16
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'DBName.dbo.TableName_Partition2' allows values that are not allowed by check constraints or partition function on target table 'DBName.dbo.TableName'.

我确定这很简单，但我不太明白。这是代码。

-- Existing structure
USE [SourceDB]
GO

CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
); 

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO

USE [DestinationDB]
GO

CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS 
 RANGE RIGHT FOR VALUES 
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme] 
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

SELECT * 
    INTO [TableName_Partition2]
FROM SourceDB.dbo.[SourceTable]
WHERE Modified >= N'2015.08.01'
  AND Modified < N'2015.09.01';
GO

CREATE CLUSTERED INDEX [TableName_Modified]  
    ON [dbo].[TableName_Partition2] ([Modified] ASC);  
GO

ALTER TABLE [dbo].[TableName_Partition2] 
    ADD CONSTRAINT [CK_TableName_Partition2] 
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2] 
    SWITCH TO [DestinationTable] PARTITION 2;
GO

DROP TABLE TableName_Partition2;
GO

我试过每个分区号，我试过将时间范围缩小到单个日期（即修改 = '2015.09.02'），但仍然出现相同的错误。我很难过。

谢谢你的帮助。

sql-server-2008-r2 partitioning

1 个回答

  1. Best Answer

    @wBob 合二为一。都是关于那些 NULL 的。

    以下脚本使用NOT NULL修改后的列创建目标表，并将 TableName_Partition2 上的同一列更改为NOT NULL创建后的列。

    这允许SWITCH运行。

    CREATE TABLE [dbo].[SourceTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
); 

CREATE CLUSTERED INDEX [SourceTable_Modified] ON [dbo].[SourceTable]
(   [Modified] ASC   )
GO


CREATE PARTITION FUNCTION [ThirteenMonthPartFunction](datetime) AS 
 RANGE RIGHT FOR VALUES 
    (   N'2015.08.01',
        N'2015.09.01',
        N'2015.10.01',
        N'2015.11.01',
        N'2015.12.01',
        N'2016.01.01',
        N'2016.02.01',
        N'2016.03.01',
        N'2016.04.01',
        N'2016.05.01',
        N'2016.06.01',
        N'2016.07.01',
        N'2016.08.01'
 );

CREATE PARTITION SCHEME [ThirteenMonthPartScheme] 
    AS PARTITION [ThirteenMonthPartFunction] ALL TO ([PRIMARY]);

CREATE TABLE [dbo].[DestinationTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NOT NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified);
GO

CREATE CLUSTERED INDEX [DestinationTable_Modified] ON [dbo].[DestinationTable]
(
    [Modified] ASC
) ON ThirteenMonthPartScheme (Modified);
GO

INSERT [dbo].[SourceTable]
VALUES  (1,1,1,1,1,9.99, '2015.08.01', 'James', 'A'),
        (2,1,1,1,1,9.99, '2015.08.02', 'James', 'A'),
        (3,1,1,1,1,9.99, '2015.08.03', 'James', 'A'),
        (4,1,1,1,1,9.99, '2015.09.01', 'James', 'A'),
        (5,1,1,1,1,9.99, '2015.09.02', 'James', 'A'),
        (6,1,1,1,1,9.99, '2015.09.03', 'James', 'A'),
        (7,1,1,1,1,9.99, '2015.10.01', 'James', 'A'),
        (8,1,1,1,1,9.99, '2015.10.02', 'James', 'A')

SELECT * 
    INTO [TableName_Partition2]
FROM  SourceTable
WHERE Modified >= N'2015.08.02'
  AND Modified < N'2015.09.01';
GO

ALTER TABLE [TableName_Partition2] ALTER COLUMN Modified datetime NOT NULL
GO

CREATE CLUSTERED INDEX [TableName_Modified]  
    ON [dbo].[TableName_Partition2] ([Modified] ASC);  
GO

ALTER TABLE [dbo].[TableName_Partition2] 
    ADD CONSTRAINT [CK_TableName_Partition2] 
    CHECK ([Modified] >= N'2015.08.01'
      AND  [Modified] < N'2015.09.01');
GO

ALTER TABLE [TableName_Partition2] 
    SWITCH TO [DestinationTable] PARTITION 2;
GO
    • 2

相关问题