Specifying Collations

Microsoft® SQL Server™ 2000 collations can be specified at several levels, including the following:

• When you install an instance of SQL Server, you can specify the default collation for that instance during setup. The default collation for the instance also becomes the default collation of the system databases: master, model, tempdb, msdb, and Distribution.
  
  
• When you create a database, you can use the COLLATE clause of the CREATE DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using SQL Server Enterprise Manager. If you do not specify a collation, the database is assigned the default collation of the model database. The default collation of the model database is the same as the default collation of the instance of SQL Server.
  
  
• When you create a table, you can specify collations for each character string column using the COLLATE clause of the CREATE TABLE statement. You can also specify a collation when you create a table using SQL Server Enterprise Manager. If you do not specify a collation, the column is assigned the default collation of the database.

  You can also use the database_default option in the COLLATE clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead of tempdb.

• When you specify a literal string, you can use the COLLATE clause to specify the collation. If you do not specify a collation, the literal is assigned the database default collation.
  
  
• In SQL-DMO you can use the Collation property to specify collations for instances, databases, and columns. For more information, see Collation Property.
  
  
• Parameters for stored procedures or functions, user-defined data types, and variables are assigned the default collation of the database:

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database. Because of this, the identifiers for variables, GOTO labels, and temporary tables are in the default collation of the instance.

Specifying collations for columns or literals can be done only for the char, varchar, text, nchar, nvarchar, and ntext data types.

Collations are generally identified by a collation name. There are two classes of names: Windows collation names for the new collations aligned with Windows locales, and SQL collation names for the compatibility mode collations that result when upgrading from earlier versions of SQL Server. For more information, see Windows Collation Name), and SQL Collation Name.

The exception to specifying collation names is in Setup:

• You do not specify a collation name for Windows collations, but instead specify the collation designator, and then select check boxes to specify binary sorting or dictionary sorting that is either sensitive or insensitive to either case or accents.
  
  
• You do not specify SQL collation names, but instead select a collation based on a longer, more human-readable display name.

You can execute the system function fn_helpcollations to retrieve a list of all the valid collation names for Windows collations and SQL collations, for example:

SELECT *
FROM ::fn_helpcollations()

You can also use the SQL-DMO ListCollations method to get a list of the valid collation names. For more information, see ListCollations Method.

The system catalog stored procedures have been enhanced to report the collation of all SQL Server objects that have a collation.

SQL Server can support only code pages that are supported by the underlying operating system. When you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. These actions can include:

• Specifying a default collation for an instance of SQL Server.
  
  
• Specifying a default collation for a database when you create the database.
  
  
• Restoring a database backup. Windows must support the code page of the default collation used by the database.
  
  
• Attaching a database. Windows must support the code page of the default collation used by the database.
  
  
• Specifying a collation for a column when creating a table.
  
  
• Specifying a collation when creating a user-defined data type.
  
  
• Specifying a collation when declaring a character-string constant.

If the collation specified or the collation used by the referenced object, uses a code page not supported by the Microsoft Windows® operating systems, SQL Server issues error 2775:

"Code page codepagenumber is not supported by the system."

Your response to this message depends on the version of the Windows operating system installed on the computer:

• Microsoft Windows 2000 supports all of the code pages used by SQL Server collations, so the error message will not occur.
  
  
• Microsoft Windows NT® 4.0 may require that you install a language pack to support some code pages. For more information about installing a Windows NT language pack, see the Windows NT Help.
  
  
• Microsoft Windows 98 supports only one code page on a computer. You must choose a SQL Server collation that uses the same code page used by Windows 98.

See Also