August 27, 2014 at 11:19 pm
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 ?
August 28, 2014 at 6:59 am
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.
August 28, 2014 at 6:34 pm
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.
August 28, 2014 at 6:53 pm
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.
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
August 28, 2014 at 8:33 pm
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