Selecting Collations

[sukishan]

Selecting Collations
In Microsoft® SQL Server™ 2000, you specify a single collation name that controls all three collation attributes: the Unicode sort order, the non-Unicode code page, and the non-Unicode sort order. None of the SQL Server 2000 collations allow different comparison and sorting rules for Unicode and non-Unicode character data. There are two groups of SQL Server 2000 collations: Windows collations and SQL collations.

Windows Collations
Windows collations are collations defined for SQL Server to support Microsoft Windows® locales. By specifying a Windows collation for SQL Server, the instance of SQL Server uses the same code pages and sorting and comparison rules as an application running on a computer for which you have specified the associated Windows locale. For example, the French Windows collation for SQL Server matches the collation attributes of the French locale for Windows.

There are more Windows locales than there are SQL Server Windows collations. The names of Windows locales are based on a language and territory, for example French (Canada). Several languages, however, share common alphabets and rules for sorting and comparing characters. For example, 33 Windows locales, including all of the Portuguese, and English Windows locales, use the Latin1 code page (1252) and follow a common set of rules for sorting and comparing characters. The SQL Server Windows collation based on the Latin1_General code page and sorting rules supports all 33 of these Windows locales.

Also, Windows locales specify attributes not covered by SQL Server Windows collations, such as currency, date, and time formats. Because countries/regions such as the United Kingdom and the United States have different currency, date, and time formats, they require different Windows collations. They do not require different SQL Server collations because they have the same alphabet and rules for sorting and comparing characters.

SQL Collations
SQL collations are a compatibility option to match the attributes of common combinations of code page number and sort orders that have been specified in earlier versions of SQL Server. For example, for mapping a SQL Server 2000 SQL collation to what is specified in earlier versions of SQL Server, the SQL Server 2000 SQL collation SQL_Latin1_General_CP1_CI_AS matches the SQL Server version 7.0 default specification of:

The ISO code page 1252.
The dictionary order, case-insensitive character sort order.

The General Unicode collation.
The SQL collations available in SQL Server 2000 do not match all combinations that can be specified in earlier versions of SQL Server. For example, no SQL Server 2000 SQL collation supports a case-sensitive sort order for non-Unicode data and case-insensitive sort order for Unicode data. The earlier SQL collations that cannot be exactly specified in SQL Server 2000 are called obsolescent SQL collations.

In SQL Server 2000, you should primarily use Windows collations. You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server, or to maintain compatibility in applications developed using SQL collations in earlier versions of SQL Server.