InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 20:32:50 GMT20http://www.sqlservercentral.com/Forums/Topic793077-146-1.aspx[quote][b]Sunil Kanta (9/24/2009)[/b][hr]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[/quote]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 [b]unique [/b]index, one should compose the columns only for performance ! (filter factors, usage,..)Thu, 24 Sep 2009 02:25:26 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic793077-146-1.aspxExactly, 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,SunilThu, 24 Sep 2009 01:22:28 GMTSunil Kantahttp://www.sqlservercentral.com/Forums/Topic793077-146-1.aspxApparently 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), [b]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.[/b]Thu, 24 Sep 2009 01:14:47 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic793077-146-1.aspxHi,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 1Cannot 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 1col2 2col3 3col4 4col5 5So 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,SunilThu, 24 Sep 2009 00:56:51 GMTSunil Kanta