Delen via

  • Artikel

Working with Collations

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, an English speaker would expect the character string 'Chiapas' to come before 'Colima' in ascending order. However, a Spanish speaker in Mexico might expect words beginning with 'Ch' to appear at the end of a list of words starting with 'C'. Collations dictate these kinds of sorting and comparison rules. The Latin_1 General collation will sort 'Chiapas' before 'Colima' in an ORDER BY ASC clause, whereas the Traditional_Spanish collation will sort 'Chiapas' after 'Colima'.

When a collation is specified for non-Unicode character data, such as char, varchar, and text data, a particular code page is associated with the collation. For example, if a char column in a table is defined with the Latin1_General collation, the data in that column is interpreted and displayed by SQL Server using the 1252 code page. For more information about code pages and collations, see Code Page Architecture.

Multiple collations can use the same code page for non-Unicode data.

Collations specified for Unicode-only data, such as nchar, nvarchar, and nvarchar(max), do not have associated code pages. Unicode data handles most universal characters. For more information, see Working with Unicode Data.

For information on new Windows collations support (collations based on Windows system locales) added in SQL Server 2008 and all other Windows collations, see Windows Collation Name (Transact-SQL).

For information on SQL Server collations, see SQL Server Collation Name (Transact-SQL).

The following topics explain collations in more detail.

Topic

Description

Collation Types

Explains the differences between Windows and SQL Server collations.

Selecting Collations

Provides guidelines for selecting a collation for character data.

Setting and Changing Collations

Explains how to set and change the collation of character data at the server, database, column, expression and identifier levels.

Collation in BACKUP and RESTORE Operations

Provides guidelines for working with collations in BACKUP and RESTORE operations.

Collations and SQL Server Client Applications

Provides information on connecting legacy client applications to SQL Server 2008.

Getting Information About Collations

Explains how to retrieve collation-related metadata in the database and server.