Description of SQL Server compatibility collations (270042)

SUMMARY

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.

MORE INFORMATION

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> ::=
Compatibility_SortOrderDecimal_lcidHex_CompStyleHex

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