Effects of changing column ordinal position

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Effects of changing column ordinal position

Effects of changing column ordinal position

Rate Topic

Display Mode

Topic Options

Author

Message

Sunil Kanta

Posted Thursday, September 24, 2009 12:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 7:02 AM
Points: 9, Visits: 74

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

ALZDBA

Posted Thursday, September 24, 2009 1:14 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 6,865, Visits: 8,071

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #793081

Sunil Kanta

Posted Thursday, September 24, 2009 1:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 7:02 AM
Points: 9, Visits: 74

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

ALZDBA

Posted Thursday, September 24, 2009 2:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 6,865, Visits: 8,071

Sunil Kanta (9/24/2009)

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

     Jul 13

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

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #793107

« Prev Topic | Next Topic »

Permissions