partition switch and non unique index

I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem.

I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting:

'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is
partitioned while index 'IX_test' is not partitioned."

Any help

Answer this question

partition switch and non unique index

KFrostILEM

Thanks for response. I red the article. Seems the problem is that my index is not aligned with the table. How to make it aligned Here is an index that I use for the source table. Partitioned table has simular index on [ACCTKEY] column as well

CREATE NONCLUSTERED INDEX [IX_T_MSB_Pseudo] ON [dbo].[T_MSB_Pseudo] (

[ACCTKEY] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
BinuJ

Please check the BOL topic below especially the section on table/index requirements:

http://msdn2.microsoft.com/en-us/library/ms191160.aspx

It would also help if you post a simple repro script demonstrating the problem. It is hard to tell what might be wrong without looking at your schema for the tables.
Dudley

It works! thanks
Shokino

You have to also create the index on the staging table in the appropriate filegroup based on the requirements for switching. The table you are switching in should reside in same filegroup as the destination partition. You can determine the filegroup by querying the catalog tables and using the $partition function. Note that the switch operation is a meta-data only operation so it requires the objects to be aligned, present in same filegroup as partition being switched and so on.

See the whitepaper below for more details:

http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/sql2k5partition.asp
chrisc12345

Hi Umachandar,

I read the article. Seems I do everything correctly but still get the error...

CREATE PARTITION FUNCTION [PF_Caldkey](int) AS RANGE RIGHT FOR VALUES (198, 199, 200)

CREATE PARTITION SCHEME [PS_Caldkey] AS PARTITION [PF_Caldkey] TO ([PRIMARY], [PRIMARY], [PRIMARY])

CREATE TABLE [dbo].[T_MSB](

[ACCTKEY] [bigint] NULL,

[CALDKEY] [int] NOT NULL,

ON [PS_Caldkey]([CALDKEY])

CREATE TABLE [dbo].[T_MSB_Pseudo](

[ACCTKEY] [bigint] NULL,

[CALDKEY] [int] NOT NULL,

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_T_MSB ON dbo.T_MSB

(

ACCTKEY

) ON PS_Caldkey(CALDKEY)

CREATE NONCLUSTERED INDEX IX_T_MSB_Pseudo ON dbo.T_MSB_Pseudo

(

ACCTKEY

) ON PS_Caldkey(CALDKEY)

--Create constraint for the current CALDKEY

ALTER TABLE [dbo].[T_MSB_Pseudo] WITH CHECK ADD CONSTRAINT [CK_T_MSB_Pseudo] CHECK([CALDKEY]=199)

--Partition switch

alter table T_MSB_Pseudo SWITCH TO T_MSB PARTITION $PARTITION.PF_Caldkey(199)

Msg 7733, Level 16, State 3, Line 1

'ALTER TABLE SWITCH' statement failed. The index 'IX_T_MSB_Pseudo' is partitioned while table 'rebar.dbo.T_MSB_Pseudo' is not partitioned.
FreddieCode

To align an index to partition scheme, you have to use the same partition scheme for the index creation too. In your code above, you are creating the index on filegroup [PRIMARY]. Instead you have to use your partition scheme like:

CREATE NONCLUSTERED INDEX [IX_T_MSB_Pseudo] ON [dbo].[T_MSB_Pseudo] (

[ACCTKEY] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

ON your_partition_scheme(<partition_columns>)
Dave Miles

what if i just drop my composite index from the table that needs to be switched

but maintain the partitioning index

Since the destination table has its own composite index

and a partioning index as well
logtorahul

I experience this also.

My destination table is partitioned by month.

I have a "from_filtered_by_month_replication" table which is also

filtered by month and have a composite pk index on 5 fields pk_x

it returns the same error as mentioned above

does the pk needs to be aligned to the partiton
NozFx

Ok, I created index aligned to partitioned schema on source and target tables. I am getting the error that my source table is not partitioned. But its not supposed to be partitioned because it holds only one month of data

Msg 7733, Level 16, State 3, Line 10

'ALTER TABLE SWITCH' statement failed. The index 'IX_T_MSB_Pseudo' is partitioned while table 'rebar.dbo.T_MSB_Pseudo' is not partitioned.
markdrury

Hello,

Regading to your following statement, does filegroup has to be the same. If I want switch to a table with index partitioned, the partion table index filegroup cannot the same as the source table as the partion table's index need to be on the partion scheme. I run into problem when creating a partition table with its primary key (I want to partition primary key) on a partition scheme. But if I leaves it to [Primary] filegroup, I am able to switch. But I want primary key index also be partitioned. Can you please help out this Thanks

3. Index definitions between tables you are trying to switch should match identically. This includes NULLability, ASC/DESC specification for columns, included columns, filegroup for partitions etc
Ljhopkins
You have to specify the partition columns with the scheme. Use the same syntax that you used to create the partitioned table. See BOL for samples on how to create aligned indexes and/or use partition schemes with indexes.
vkv

I have more questions.I use the following statement to create partion function and tables:

CREATE PARTITION FUNCTION Party_PFN(int)

AS

RANGE LEFT FOR VALUES (0, 8588394)

GO

CREATE PARTITION SCHEME [Party_PScheme]

AS

PARTITION Party_PFN

all TO ([PRIMARY])

CREATE TABLE [dbo].[Party_O](

[PartyID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PhoneAreaCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

CONSTRAINT [Party_PK_O] PRIMARY KEY CLUSTERED

(

[PartyID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Party_p](

[PartyID] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PhoneAreaCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

,

CONSTRAINT [Party_PK_P] PRIMARY KEY CLUSTERED

(

[PartyID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [Party_PScheme](PartyID)

Question 1: When I check the property of Party_p table, why does it say partion is FALSE.

Question 2: I am able to SWITCH and SPLIT Party_p] table. But when I run

select * from sys.partitions where object_name(object_id)='Party_P'
It always show one row.
Peter De

CREATE NONCLUSTERED INDEX [IX_T_MSB_Pseudo1] ON [dbo].[T_MSB_Pseudo] (

[ACCTKEY] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

ON PS_Caldkey

I am getting error message when creating index aligned to partition scheme PS_CALDKEY:

Msg 2726, Level 16, State 1, Line 2

Partition function 'PF_Caldkey' uses 1 columns which does not match with the number of partition columns used to partition the table or index.
Andrew Mercer

Thanks for posting the script. It was useful to determine the reason for the failure. I will try to explain completely why the switch doesn't work in your case. First, let us take the nonclustered non-unique index IX_T_MSB that you created on the partitioned table:

CREATE NONCLUSTERED INDEX IX_T_MSB ON dbo.T_MSB

      (

      ACCTKEY

      ) ON PS_Caldkey(CALDKEY)

You can notice that the partitioning column "CALDKEY" is not part of the index column(s) definition i.e., it is not part of the key column(s). But you have specified a partition scheme in the ON clause which implies that the index should be partitioned. So in this case, SQL Server will automatically add the column "CALDKEY" to the index definition but as included column at the leaf level of the index. Included column is a new feature in SQL Server 2005 where you can add columns to a non-clustered index (to basically create a sort of covering index or add frequently accessed columns to index without incurring overhead of going to table for certain queries). Now, your index definition actually has two columns and not one. This part is documented in the CREATE INDEX topic under the ON <partition_scheme> section. You can observe the index definition by running the query below:

select * from sys.indexes as i join sys.index_columns as ic
on ic.object_id = i.object_id and ic.index_id = i.index_id
where i.name= 'IX_T_MSB' and i.object_id = object_id('dbo.T_MSB')

The switch operation now fails because the index on your non-partitioned table has only one column. In fact, in your definition you are partitioning the index which is incorrect. You want to just specify the FILEGROUP directly in the index like:

CREATE NONCLUSTERED INDEX IX_T_MSB_Pseudo ON dbo.T_MSB_Pseudo

      (

      ACCTKEY

      ) ON PRIMARY

Now, this will still fail because the columns in the two indexes do not match. To correct the problem, you can do the following:

CREATE NONCLUSTERED INDEX IX_T_MSB_Pseudo ON dbo.T_MSB_Pseudo

      (

      ACCTKEY

      )

INCLUDE (

CALDKEY

)

ON PRIMARY

You can include the column "CALDKEY" as part of the index definition on the non-partitioned table. Now, the indexes will be compatible and your switch operation will succeed.

Lastly, it is not clear if you want to partition the index like you did using a partition scheme but not specifying the partitioning column as part of the key column(s). This index is compact since it doesn't include partitioning column but it is partitioned. It will be useful for queries based on just ACCTKEY alone. But if you are looking for ACCTKEYs in specific partitions it is not useful since SQL Server has to scan the entire index (partition column is part of leaf level of the non-clustered index and not part of b-tree). Maybe your intent was to include the partitioning column also in the index definition. It is still useful to create such indexes depending on your access pattern and queries.

Hope this helps you understand the switch operation more and how the partitioning works in your specific example. So overall for switch operation to work, the following needs to be satisfied (it is not exhaustive list, BOL contains that):

1. Indexes on the partitioned table should be partitioned (ON <partition_scheme> should be used on all indexes)

2. In cases where the partitioning column(s) is not part of the index key column(s) SQL Server will automatically add them so they are partitioned. This part is different for non-clustered since the partitioning column will be part of leaf-level

3. Index definitions between tables you are trying to switch should match identically. This includes NULLability, ASC/DESC specification for columns, included columns, filegroup for partitions etc

4. CHECK constraints need to be specified on the table you are switching to restrict data to specific partition. This constraint should also be trusted meaning all data in the table needs to be evaluated. (Add constraints using WITH CHECK option)
partition switch and non unique index

Answer this question