1

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?

  • 3
    I haven't been able to recreate your problem. But I had to fix the typos, remove the default constraint (don't have that sequence on by system and it's named with a reserved keyword). Please post a complete script that reproduces the error with all the prerequisites (partition function, scheme, create schema, create sequence) that can be run against an empty database. I suspect you will find the problem by going through that exercise. – Dan Guzman Jun 17 '15 at 11:14
  • @DanGuzman - You were right. I setup a repro and it worked fine. I am guessing that somehow I got my tables crossed while renaming from the olde table to the new. So I was trying to swap the partition of the wrong table (That did not have the right compression). If you want to post an answer about making sure your source table is what you think it is, I will accept it. – Vaccano Jun 17 '15 at 16:44
  • You can check this bug if it helps support.microsoft.com/en-us/kb/974262 – Omesh Sep 8 '15 at 6:21
2

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

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.