Sql Server thinks I have different Data Compression when swaping partitions

Question

I am setting partition switching for my database.

I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping.

Here is an example:

--:setvar IsArchive ""
--:setvar Suffix _Partitioned

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR order.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,   
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED 
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE)
GO

I run it once with the top two :setvar options and again with the bottom two :setvar options.

So they are very similar with mostly only name differences:

• [Orders].[Order_Partitioned]
• [ArchivedOrders].[Order_Archived]

When I run this:

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1 
      TO ArchivedOrders.Order_Archived PARTITION 1

I get the following error:

ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.

But they clearly must be the same! Or at least it seems to me that they must be the same.

Why would Sql Server think that they are different data compressions?

Answer 1

Below is a complete script that works as expected without the error.

:setvar IsArchive ""
:setvar Suffix _Partitioned

CREATE SCHEMA Orders;
GO

CREATE SCHEMA ArchivedOrders;
GO

CREATE SEQUENCE Orders.NextOrderId;
GO

CREATE PARTITION FUNCTION OrderIdPartitionFunction(bigint) AS
    RANGE RIGHT FOR VALUES();
GO

CREATE PARTITION SCHEME OrderIdPartitionScheme AS 
    PARTITION OrderIdPartitionFunction ALL TO ([PRIMARY]);
GO

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,   
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED 
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

:setvar IsArchive Archived
:setvar Suffix _Archived

CREATE TABLE [$(IsArchive)Orders].[Order$(Suffix)](
    [OrderId] [BIGINT] NOT NULL DEFAULT(NEXT VALUE FOR Orders.NextOrderId),
    [OtherStuff] [VARCHAR](100) NOT NULL,   
 CONSTRAINT [PK_Order$(Suffix)] PRIMARY KEY CLUSTERED 
(
    [OrderId] desc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) ON OrderIdPartitionScheme(OrderId) WITH (DATA_COMPRESSION=PAGE);
GO

ALTER TABLE Orders.Order_Partitioned SWITCH PARTITION 1 
      TO ArchivedOrders.Order_Archived PARTITION 1;
GO