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.