November 29, 2010 at 4:24 am
Help required please.
I'm trying to write a procedure to dynamically create a non-partitioned table to receive the a partition switched out of a partition table.
Everything was working until I came across a partitioned table that had an index defintion in the format:
[font="Courier New"]create nonclustered index IDX_1 on partionedTable (col_1, col_2) on partion_scheme(col_3)[/font]
I initially created the index on the target table as:
[font="Courier New"]create nonclustered index tgt_idx_1 on targetTable (col_1, col_2)[/font]
However, ALTER TABLE SWITCH complains that the index definitions do not match.
In sys.index_columns
column index_column_id key_ordinal partition_ordinal is_included_column
col_1 1 1 0 0
col_2 2 2 0 0
col_3 3 0 1 0
(sorry about table formatting)
All keys are ascending order
How should I map the use of col_3 in the index on the target table since my original assumption that since it's only a partition_ordinal and therfore not requirfed appears to be flawed?
December 1, 2010 at 2:08 am
I think that I've got an answer to my own question.
I believe that matching constraints excluding foreign keys, primary key and aligned indexes need to be created on the non-partitioned target of the "alter table switch" statement.
An aligned index is an index within which the partitioning column(s) are used within the index's keys. That is an entry within sys.index_columns has (key_ordinal > 0 and partition_ordinal > 0).
What I don't understand is what happens to partitioned non-aligned indexes after the switch. Does anybody know? It appears that the index partition is simply dropped.
May 14, 2012 at 11:15 am
I'm studying for the 432 exam and I'm getting this error when attempting the ALTER TABLE SWITCH statement.
The message suggests there is no index on the source DB indentical to the clustered index on the target DB, BUT THERE IS. In fact, the indexes on the target DB were created by scripting out CREATE TO the indexes on the Source DB, changing the DB names and running them on the Target DB. As far as I can tell, the indexes are identical.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply