Column Rename

  • Hi All,

    Today I had to rename the column for few tables and the old column name was present in the index for that table.

    After rename the new name was updated in all the indexes referring it.

    Was it an Index rebuilt Or how it was applied internally. I couldn't see the call to indexes even in the query plans or logs ?

  • WILD GUESS!

    I'm thinking that internally to SQL Server, the column is referenced by an ordinal value, like column number. When displaying information about the index to the user in ssms for instance, thats when any human readable name would be linked to it, and also of course when interpreting code / references to the column name.

  • The primary key on the internal storage for columns is object_id (of the table) and columnid (which is an incrementing number) and only unique for that object_id.

    Similarly the internal storage for index column usage references the object_id of the table and the columnid. Column Name is merely an attribute.

  • The name is dimension information. Your reference table can be most easily found through the sys views:

    SELECT * FROM sys.indexes

    select * from sys.index_columns

    You'll notice it stores the column_id, locatable via

    SELECT * FROM sys.syscolumns, along with the id of the object.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks all for clarifying

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply