Skip to main content
Database Administrators

Return to Question

deleted 2 characters in body
Source Link
Vaccano
Vaccano
  • 2.5k
  • 5
  • 31
  • 56

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 [OrderId]PartitionSchemeOrderIdPartitionScheme(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?

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 [OrderId]PartitionScheme(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?

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?

Source Link
Vaccano
Vaccano
  • 2.5k
  • 5
  • 31
  • 56

Sql Server thinks I have different Data Compression when swaping partitions

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 [OrderId]PartitionScheme(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?