2

Working on some dynamic SQL to create a 13 month rolling partitioned table. See here for some background info.

The dynamic SQL part is simple enough, and the first partition switches out without a problem. Unfortunately when I get to the second partition I start getting this error:

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'.

I'm sure it's something simple but I can't quite figure it out. Here is the code.

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

I've tried each partition number, I've tried reducing the time range to a single date (ie modified = '2015.09.02') but still get the same error. I'm stumped.

Thanks for any help.

  • Should you be using DATE rather than DATETIME? Check the NULLability of the Modified column. Chances are the SELECT INTO creates it as NULLable when it should not be. I have also seen this occur with constraints becoming untrusted. Check their status with SELECT OBJECTPROPERTY( OBJECT_ID('CK_TableName_Partition2'), 'CnstIsNotTrusted') CnstIsNotTrusted;. Set them to trusted again if required. – wBob Jul 5 '16 at 23:08
  • @wBob I'd have been surprised if it wasn't trusted since I'm creating the constraint on the spot. The column is NULLable but that shouldn't affect the constraint right? Also the source table is NULLable & the have to be the same. – Kenneth Fisher Jul 6 '16 at 1:11
  • Are CompassAudit.dbo.[TableName] and just [TableName] the same table? Does CompassAudit.dbo.[TableName] use the partition function and scheme in the code above? – James Anderson Jul 6 '16 at 7:47
  • @JamesAnderson oops, forgot to change that DB name. That's meant to be SourceDB. That's the initial source of the data. No. Straight up [TableName] is the destination DB. I'll make a note of that in the code too. – Kenneth Fisher Jul 6 '16 at 14:16
  • Can we see the CREATE script for the destination table [TableName] complete with it's indexes and constraints? – James Anderson Jul 6 '16 at 14:22
2

@wBob got it in one. It's all about those NULLs.

The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation.

This allows the SWITCH to run.

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
| improve this answer | |
  • So even though the source table allows nulls in the column the partitioned version can't? Makes sense I guess since it's the partitioning column. – Kenneth Fisher Jul 6 '16 at 17:06
  • That would be a yes. Partitioned column can not be null if you want to do a switch. – Kenneth Fisher Jul 6 '16 at 20:17
  • That's not quite true. I got this to work by making the columns non-nullable (here) or having the staging table on the same partitioning scheme like here. I wouldn't personally use a nullable partitioning column due to problems like this. This is also a recommendation from MS: "We recommend that you specify NOT NULL on the partitioning column of partitioned tables. ...". Could your column be updated therefore incurring the risk of data movement? – wBob Jul 6 '16 at 20:55
  • I tried to get it working with the NULLs by including [Modified] IS NOT NULL in the constraint but no good. If modified is a column that is updated often it is probably a bad choice for a partitioning key but can it cause data movement if the partitioning scheme is all to PRIMARY ? – James Anderson Jul 7 '16 at 7:33
  • @JamesAnderson I was thinking the scheme was just for the repro - you wouldn't otherwise do it that way. – wBob Jul 7 '16 at 9:22

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.