3

We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation:

In the Properties window for the database we have the following:

  • Under the "General"-tab and Maintenance it says: Collation: Compatibility_60_406_30001
  • Under the "Options"-tab the Collation is blank in the drop Down (all though many collations are available for choosing in the list)

I tried googling the collation Compatibility_60_406_30001, but the search came up empty. Anyone have any suggestions on what to do here? So far I have not seen any errors becaus of this, but I would like to choose a valid collation for this database also.

  • 1
    the collation value would be there 'SQL_Latin1_General_CP1_CI_AS' mssqltips.com/sqlservertip/2901/… – Md Haidar Ali Khan Oct 19 '15 at 8:26
  • DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRONMENTS – Md Haidar Ali Khan Oct 19 '15 at 8:28
  • @MdHaidarAliKhan Thanks for Your comment. The link you sent talks about changing the server collation, is there a similar way to do this for just the one database? – GHauan Oct 19 '15 at 8:41
  • @GHaunan, yes , you can also change the user databse collation. – Md Haidar Ali Khan Oct 19 '15 at 8:46
  • @GHaunan, can you check through SELECT SERVERPROPERTY(N'Collation'). – Md Haidar Ali Khan Oct 19 '15 at 8:47
3

Looks like you have an old compatibility collation, which comes from SQL Server 2005 or SQL Server 2000. The format of this compatibility collation name is:

Compatibility_SortOrderDecimal_lcidHex_CompStyleHex

Explanation:

SortOrderDecimal 60 = Case-insensitive Scandinavian dictionary sort order, without case preference lcidHex (Locale id) 0x406 = 1030 (Danish) CompSytleHex Default Unicode comparison style 0x30001 = Ignore case, Ignore Kana, Ignore width

You can change the database collation through the following code:

USE master;
GO
ALTER DATABASE YourDatabaseName
COLLATE Danish_Norwegian_CI_AS;
GO

If you must have backward compatibility with the SQL collation, you could use the SQL_Scandinavian_CP850_CI_AS collation.

  • Thank you, this sounds plausible (given the history of the database). Would this now match the SQL_Scandinavian_CP850_CI_AS collation? (This is the collation on the server Level). – GHauan Oct 19 '15 at 8:44
  • Looks to me the right choice GHauan, I updated my answer. – Peter Elzinga Oct 19 '15 at 9:01
  • 1
    Sorry, was too soon with my answer. Don't use SQL_ collations, because these are old (becoming obsolete) collations. The one you should use I will post in my answer. – Peter Elzinga Oct 19 '15 at 9:04
  • 1
    When this is the correct answer, I would appreciate if you could mark it as answer :-) – Peter Elzinga Oct 19 '15 at 9:19
  • 1
    Well, in general it is best to keep the collation sequence of your databases the same as the collation sequence of your server. The easiest way to change the collation sequence of your server is to reinstall it and specify the new collation sequence. The problems you could run into is mainly the sorting order of your data. But if you pick the wrong collation sequence and have unique constraints in your tables on character columns, you could run into duplicate value problems. – Peter Elzinga Oct 19 '15 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.