Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Effects of changing column ordinal position Expand / Collapse
Author
Message
Posted Thursday, September 24, 2009 12:56 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:59 AM
Points: 9, Visits: 77
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

--------------------------------------------------------------------------

"If you’re not prepared to be wrong, you’ll never come up with anything original."

Post #793077
Posted Thursday, September 24, 2009 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 7,004, Visits: 8,448
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #793081
Posted Thursday, September 24, 2009 1:22 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:59 AM
Points: 9, Visits: 77
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

--------------------------------------------------------------------------

"If you’re not prepared to be wrong, you’ll never come up with anything original."

Post #793086
Posted Thursday, September 24, 2009 2:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 7,004, Visits: 8,448
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #793107
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse