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. But 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, while 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 code points of the 1252 code page. For more information about code pages and collations, see Collation and Code Page Architecture.

Collations specified for Unicode data, such as nchar, nvarchar, and nvarchar(max), do not have specific code pages associated with them, because Unicode data handles virtually all characters of all the world's languages. For more information, see Working with Unicode Data.

The following topics explain collations in more detail.

Topic Description

Collation Types

Explains the differences between Windows and SQL 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 Precedence (Transact-SQL)

Explains the rules of conversion in expressions involving multiple collations.

Collation in BACKUP and RESTORE Operations

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

Copying Data Between Different Collations

Provides guidelines for working with collations when you use the bcp utility and the BULK INSERT command.

Collations in Distributed Queries

Provides guidelines for working with collations in linked server operations.

Collation and Code Page Architecture

Explains the relationship between code pages and collations.

Getting Information About Collations

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

Change History

Release History

17 July 2006

Changed content:
  • Replaced reference to ntext data type with nvarchar(max) data type.