Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Changing collations Expand / Collapse
Posted Thursday, March 24, 2011 3:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 13, 2016 4:04 AM
Points: 3,358, Visits: 1,552
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.
Post #1083143
Posted Saturday, March 26, 2011 11:03 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 9:19 AM
Points: 9,822, Visits: 11,891
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.

Post #1084455
Posted Friday, March 9, 2012 2:15 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 17, 2015 3:03 PM
Points: 504, Visits: 259
I learnt something new and thanks for the great question.
Post #1264619
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse