4

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.

Improve this question
6
  • 1
    the collation value would be there 'SQL_Latin1_General_CP1_CI_AS' mssqltips.com/sqlservertip/2901/…
    – Md Haidar Ali Khan
    Commented Oct 19, 2015 at 8:26
  • DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRONMENTS
    – Md Haidar Ali Khan
    Commented Oct 19, 2015 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
    Commented Oct 19, 2015 at 8:41
  • @GHaunan, yes , you can also change the user databse collation.
    – Md Haidar Ali Khan
    Commented Oct 19, 2015 at 8:46
  • @GHaunan, can you check through SELECT SERVERPROPERTY(N'Collation').
    – Md Haidar Ali Khan
    Commented Oct 19, 2015 at 8:47

1 Answer 1

Reset to default
4

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.

Improve this answer
8
  • 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
    Commented Oct 19, 2015 at 8:44
  • Looks to me the right choice GHauan, I updated my answer.
    – Peter Elzinga
    Commented Oct 19, 2015 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
    Commented Oct 19, 2015 at 9:04
  • 1
    When this is the correct answer, I would appreciate if you could mark it as answer :-)
    – Peter Elzinga
    Commented Oct 19, 2015 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
    Commented Oct 19, 2015 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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