SQL Server Configuration - Collation

SQL Server Setup

On the Server Configuration - Collation page of the SQL Server Installation Wizard, you can modify collation settings that the Database Engine and Analysis Services use for sorting purposes. Select the option to match collation settings of different installations of SQL Server, or of another computer.

Options

Customize for SQL Server and Analysis Services

SQL Server provides two groups of collations: Windows collations and SQL Server collations. You can specify separate collation settings for the Database Engine and Analysis Services, or you can specify the same collation for both.

By default, a SQL Server collation is selected for US-English system locales. The default collation for localized versions of SQL Server is determined by the Windows system locale setting for your computer. For more information, see Using SQL Server Collations.

The default settings should be changed only if the collation setting for this installation of SQL Server must match the collation settings used by another instance of SQL Server, or if it must match the Windows system locale of another computer.

Note   Analysis Services uses Windows collations only. If you plan to install Analysis Services, select a Windows collation during SQL Server Setup to ensure consistent results between the SQL Server Database Engine and Analysis Services.

For more information, see Collation Settings in Setup.

Best Practices

For a table of Windows System locales and the corresponding default collations used by SQL Server Setup, see Collation Settings in Setup.

If it is possible, use a single collation for your organization. This way, you do not have to explicitly specify the collation for every database, column, expression, or identifier. If you must work with multiple collations and code page settings, code your queries to consider the rules of collation precedence. For more information see the Books Online topic for Collation Precedence (Transact-SQL).

When you select a collation for SQL Server, consider the following recommendations:

  • Select a binary collation if binary ordering is acceptable.
  • If your applications use both nchar/nvarchar and char/varchar data types and can mix them in comparison, select a Windows collation for consistent comparison across data types.
  • If your applications use only char/varchar data types and you use Analysis Services, select a Windows collation.
  • If your applications use only char/varchar data types and all of the following are also true, select a SQL Server collation:
    • You are concerned about string comparisons/LIKE performance.
    • You are not concerned about the linguistic comparison semantics in Windows collations.
    • You want the dictionary sort order in SQL Server collations.
    • You are not concerned about sorting consistency with the operating system or with other applications.

See Also