June 30, 2009 at 6:05 am
Hopefully this will be an easy question to answer. My company recently ran into some issues with Collations not matching so we had to change the database to a different collation to match the server due to using temp tables with different collations than the server. My question becomes: can changing a collation on a table affect the indexes? For example, a clustered index affects the physical ordering of the records. If I had a clustered index that had text in it (not saying you should) and you switched from a Case Insenstitive collation to a Case Senstitive collation, is it possible SQL Server would have to "recreate" the index to match the order? What about non-clustered indexes? This is partially curiosity, but a huge database could be affected pretty severely if something does need to get shuffled.
Cheers,
Brian
June 30, 2009 at 8:52 am
Sorting depends on collation (or can depend on collation).
In which case one would expect a clustered index on varchar etc columns to have to be rebuilt to take into account the new order..?
I'm thinking aloud here, hopefully one of the experts can confirm or deny this
June 30, 2009 at 9:06 am
Afternoon All,
The physical storage of character strings in SQL Server is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.
So a physical change (resulting from a specific collation change i.e. by using ALTER TABLE and COLLATE) to the column data would therefore cause associated index structures to be modified too.
Changing the database collation, by using ALTER DATABASE does NOT change the collation of any existing columns. See Books online "Setting and changing Database Collation" for reference.
For further reading, check out the books online documentation: Collation and International Terminology
Hope this helps.
June 30, 2009 at 9:25 am
Thanks for the responses. That is what I thought would happen. I did read some more information after posting. It appears that changing the collation DOES NOT actually change the indexes. You have to rebuild the indexes to get them to change. YIKES!
Cheers,
Brian
June 30, 2009 at 9:38 am
Hi Brian,
Good stuff. I have eddited my prior post for improved clarity.
June 30, 2009 at 9:45 am
Thanks John! This sounds like the makings of a good QotD!
Cheers,
Brian
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply