Sql-server – Collation Compatibility_60_406_30001 in SQL Server

collationcompatibility-levelsql server

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.

Best Answer

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.

Related Question