Description of SQL Server compatibility collations

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q270042
This article has been archived. It is offered "as is" and will no longer be updated.
In SQL Server 2000, every collation has a unique name. SQL Server 2000 Books Online documents the format of collation names for SQL Server collations and Microsoft Windows collations, the two most common classes of collations. However, there is a third class of collation called a "compatibility collation" (or "obsolescent collation") that has a different name syntax.

Compatibility collations are only used on databases that have been upgraded from a Microsoft SQL Server 7.0 server or a Microsoft SQL Server 6.5 server that had a combination of code page, sort order or Unicode collations that are not supported by any of the standard SQL Server or Windows collations.
Following is the general structure of a collation name in SQL Server:

<Collation_name> ::= <Windows_collation_name>
| <SQL_collation_name>
| <Compatibility_collation_name>
<Windows_collation_name> is defined in the SQL Server 2000 Books Online topic "Windows Collation Name". <SQL_collation_name> is defined in the Books Online topic "SQL Collation Name". <Compatibility_collation_name> is not defined in SQL Server Books Online.

The format of a compatibility collation name is:
<Compatibility_collation_name> ::=
The sort order in a compatibility collation is represented with a decimal number. The locale ID (LCID) and the Unicode comparison style are represented with hexadecimal numbers. For example, if a database collation is named "Compatibility_51_409_30001", it should be interpreted as:
Sort order 51 (Dictionary order, case-sensitive)
LCID 0x409 = 1033 (General Unicode)
Default Unicode comparison style 0x30001 = 196609 (case insensitive, accent sensitive, Kana insensitive, width insensitive)
You can find a list of sort order IDs in the SQL Server 7.0 Books Online topic "Sort Order IDs". Unicode locale IDs are in the SQL Server 7.0 Books Online topic "Unicode Collation". You can find a description of the Unicode comparison style bits in the following article in the Microsoft Knowledge Base:
233312 INF: Customizing SQL/MSDE Unattended Installation Files
compatibility collation designator

Article ID: 270042 - Last Review: 12/05/2015 21:16:36 - Revision: 4.3

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbnosurvey kbarchive kbinfo KB270042