SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing collations


Changing collations

Author
Message
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3594 Visits: 1552
Thanks for the kind comments everyone.

This question was written after I'd spent a day or so changing the collation on a database and found that before I could even change the DB collation I had to drop all the computed columns, check constraints and un-schemabind any schemabound functions and views.

As to then changing individual columns, I had to drop/disable indexes and foreign keys, change the collation on the columns and then rebuild everything. All quite labour intensive.

The reason I did it was because I'd restored a SQL collated database with some SQL and some Windows collated columns to a Windows collated server. It was then complaining about stored procedures which created temp tables and joined them to existing tables on character columns. I assume that the collation in the tempdb must have been the same as the server collation, which would explain the problem. The mix of collations (I've no idea how it got like that by the way) had always bugged me in the past so I decided to sort it out once and for all.

If anyone is interested, Erland Sommarskog has raised a suggestion on Connect to make it easier to change the collation on a database.
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14415 Visits: 12215
Good question. I found it easy because I've been through the pain of changing collations a couple of times (right to the point of rebuilding master to fix the server default collation).

Actually SQL Server is a a bit of a pain in its collation handling. Erland's Connect suggestion will go some way towards alleviating that pain (if MS takes it up - so everyone please vote for it) but only a small part of the way.

There is no good reason for a schemabound view to constrain the database default collation is it makes no use of it, its select statement uses no CTE that defines a column without an explicit collation being specified. Similarly for a check constraint, or a computed column, if no reference is made to the database default collation (so for example no string literals with default collation) there's no reason to object to a default collation change. So life could be made a little easier if MS would change the code to check whether the various objects are impacted by the default collation, and if none are allow a change of the default collation (perhaps with a warning, if MS thinks it's needed).

Then there's changing the collation of a column involved in one or more indexes: can we have an ALTER...COLLATION WITH REINDEX option? Having to drop the index and recreate it isn't too much of a pain, but it begins to get interesting when the column concerned is part of a key constraint, because if the column involved is part of a key there's the foreign key issue. Couldn't we have "on collation change cascade" as an option on foreign key constraint declaration? (OK, bad terminology, it's not cascading to lots of affected rows it's changing metadata -and that may need rebuilding some indexes.) Or is that going too far? Even without the foreign key issue there's the basic key issue - changing the collation may remove uniqueness - I need to be able to have the system tell me that my key will no longer be a key if I make the suggested collation change, not fine out the hard way (I recall that there's an issue with some digraphs and their single character equivalents for example is <thorn> equal to "th").

An interesting bunch of issues to be suggested by a QoTD.

Tom

zymos
zymos
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 259
I learnt something new and thanks for the great question.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search