Description of SQL Server compatibility collations

Article translations Article translations
Article ID: 270042 - View products that this article applies to.
This article was previously published under Q270042
Expand all | Collapse all

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

Properties

Article ID: 270042 - Last Review: February 22, 2007 - Revision: 4.3
APPLIES TO
  • 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
Keywords: 
kbinfo KB270042

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com