Home Dashboard Directory Help
Search

Make it easy to change collation on a database by Erland Sommarskog


Status: 

Active


77
0
Sign in
to vote
Type: Suggestion
ID: 358320
Opened: 7/28/2008 1:49:09 PM
Access Restriction: Public
0
Workaround(s)
view

Description

It's great to be able to have different collations on the same server, and even in a
different columns. But in many cases, you are not interested in having a multi-collation
server, but you only want a single collation. I have submitted several other suggestions
to minimise the impact and the risk for it to happen. However, even if all these are implemented, there will still be users sitting with a datbase that for one reason or another have the wrong collation. Today they are very much left on their own. You can change the
database collation with ALTER DATABASE, but it only changes the collation for metadata, and the restrictions precludes it from being usable for a database of any complexity. (What?
CHECK constraints not permitted?)

One way would be put this a tool of some sort, for instance the Copy Database Wizard.
However, it's hardly any secret that I have don't any high regards for CDW. I think this
should be in the engine.
Details
Sign in to post a comment.
Posted by Piquet on 4/9/2014 at 8:22 PM
Currently there are a number of objects in the database that can prevent the collation for the database (or a column in the database) from being made, including Indexes, Constraints, computed columns, etc..

Even if all related Indexes and constraints are DISABLED (or set as NOCHECK) SQL still does not permit the collation for a column to be changed.

It would be a big step forward if collation change was permitted on a column where related indexes, constraints etc. were all disabled, and the new collation used when these objects were re-enabled...
Posted by AussieJohn on 5/8/2012 at 5:48 PM
Any chance of changing "ALTER DATABASE ... COLLATE ..." to have three options?
1. NEW
the current behaviour - new columns have the new collation
2. REPORT
change database collation and report columns that now have different collation
3. ALL
change database collation and all column collations in that database, dealing with any pesky constraint issues.

Many users expect ALTER DATABASE COLLATE to change the collation database wide. It certainly would help many DBAs and organisations to be able to do this.
Posted by Josh Gallagher on 4/16/2012 at 7:18 AM
I've just tried out the ALTER DATABASE on SQL Server 2012 RTM and the issue still exists. Any computed columns in the database still prevent the database collation from being changed.
Posted by Geir Andre on 2/1/2012 at 2:24 AM
Hi, will this be a feature of the SQL Server 2012?
Posted by Microsoft on 8/8/2008 at 11:02 AM
Hello,

Thank you for sharing your ideas with us. We are aware of this limitation of SQL Server 2008 and we're considering fixing it in the next release.

Thanks,
Krzysztof Kozielczyk
Sign in to post a workaround.