September 24, 2009 at 12:56 am
Hi,
Recently worked on moving data and indexes to new filegroups, in this process I observed that while moving a CLUSTERED COMPOSITE INDEX if the column ordinal position has changed the following script fails,
CREATE UNIQUE CLUSTERED INDEX [PK_FACT]
ON [DW001].[DBO].[TBL_FACT]([col1], [col3], [col4], [col5], [col2] )
WITH DROP_EXISTING ON [DATA] ;
ERROR:
Msg 1907, Level 16, State 2, Line 1
Cannot recreate index 'PK_FACT'. The new index definition does not match the constraint being enforced by the existing index.
The above script was generated by an SP (unfortunately that was designed by ME). After some R&D and scratching my DBA head, found out that the Column Ordinal potion provided in the script is different from the existing ordinal potion (which is as given below),
Key Column Key Ordinal
------------------------------------
col1 1
col2 2
col3 3
col4 4
col5 5
So I went on and modified the script as below,
CREATE UNIQUE CLUSTERED INDEX [PK_FACT]
ON [DW001].[DBO].[TBL_FACT]([col1], [col2], [col3], [col4], [col5] )
WITH DROP_EXISTING ON [DATA] ;
Alas!! it worked.............!! Finally I corrected my SP by adding 'ORDER BY key_ordinal ASC'.
I used the same SP (Old Version) to generated scripts for moving NON-CLUSTERED COMPOSITE INDEX and the scripts worked fine. This is because, column ordinal position can be altered for non-clustered indexes. But my concern is, I am not sure if the SP generated scripts with existing ordinal position.
Questions:
1. How badly is this ordinal position alteration gonna effect tables after movement?
2. Will there be any problems while query processing?
Please help me.
Thanks in Advance,
Sunil
Regards,
SMK
September 24, 2009 at 1:14 am
Apparently the index you tried to move was the one supporting the primary key of your table.
That index must 100% match your tables PK definition.
Altering that column sequence results in a drop and re-create of the primary key constraint !
Also keep in mind, the (uniquified if needed) clustering index key will be added to all NCI !
That's one of the reasons why you should keep your clustering index as narrow as possible.
Also keep in mind that the actual data resides IN the clustering index (at leaf level), so altering the clustering index's column order will cause the clustering index to be rebuilt, hence, 100% rewritten and all NCI will have to be modified to get the new CLIX key ref.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2009 at 1:22 am
Exactly, I could later manage to reorder for PK index. And I did this only because i got a error while moving.
But, what for Non-Clustered Composite Index? I din't get any error and it moved sucessfully. I am worried about the indexed column order now. Does it gonna effect db performance?
Regards,
Sunil
Regards,
SMK
September 24, 2009 at 2:25 am
Sunil Kanta (9/24/2009)
Exactly, I could later manage to reorder for PK index. And I did this only because i got a error while moving.But, what for Non-Clustered Composite Index? I din't get any error and it moved sucessfully. I am worried about the indexed column order now. Does it gonna effect db performance?
Regards,
Sunil
That will depend on the actual data content ( cardinality and organisation ) and the way your queries use the source object !
One should not modify and indexes column order if one doesn't have performance reasons to do so !
In a unique index, one should compose the columns only for performance ! (filter factors, usage,..)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply