Problem with a partitioning switch script

Question

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.

Answer 1

@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