Collation Settings
SQL Server Express users: click Collation Settings (SQL Server Express) for information that is specific to SQL Server Express Setup.
Use the Collation Settings page of the Microsoft SQL Server Installation Wizard to modify default collation settings used by the Database Engine and Analysis Services for language and sorting purposes. Choose the Collation Designator option to match collation settings of different installations of SQL Server or of another computer. Use the SQL Collations option to match settings that are compatible with the sort orders in earlier versions of SQL Server.
Options
- Customize for each service account
You can specify separate collation settings for the Database Engine and Analysis Services, or you can specify a single collation for all of the services. If you select both the Database Engine and Analysis Services for your installation, you can select the check box next to Customize for each service account to activate the list of services in the drop-down list. Select a service from the drop-down list and then make your collation and sort order selections for that service. After you have made your selections, click Next to continue.
Collation designator and sort order
Designate the collation to be used by this instance of SQL Server 2005. A SQL collation is selected by default for English-language system locales. The default collation for non-English locales is the Microsoft Windows system locale setting for your computer - the Language for non-Unicode programs setting, or the closest equivalent from the Regional and Language Options in Control Panel.Change the default settings 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 SQL collations cannot be used for Analysis Services. If you select SQL collations for use with your Database Engine installation, SQL Server Setup will select a best match Windows collation for Analysis Services, based on the SQL collation you select. If the Database Engine and Analysis Services collations do not match, you may receive inconsistent results. To ensure consistent results between the Database Engine and Analysis Services, use Windows collations.
For more information, see Collation Settings in Setup.
- Sort order
Specify the sort order to use with the Collation designator you have selected. Binary is the fastest sorting order and is case-sensitive. If Binary is selected, the Case-sensitive, Accent-sensitive, Kana-sensitive, and Width-sensitive options are not available. For more information, see Windows Collation Sorting Styles.
SQL collations
Match settings compatible with SQL Server versions 8.0, 7.0, or earlier. The SQL collations option is used for compatibility with earlier versions of SQL Server. For more information, see Using SQL Collations.Note
SQL collations cannot be used for Analysis Services. If you select SQL collations for use with your Database Engine installation, SQL Server Setup will select a best match Windows collation for Analysis Services, based on the SQL collation you select. If the Database Engine and Analysis Services collations do not match, you may receive inconsistent results. To ensure consistent results between the Database Engine and Analysis Services, use Windows collations.
Typical User Scenarios
For a table of Windows System Locales and the corresponding default collations used by SQL Server Setup, see Collation Settings in Setup.
Best practices include use of a standardized collation for your organization, if possible. Using a standard collation setting across all systems in your organization will help to eliminate the need to explicitly specify the collation in every character or Unicode expression. If you must work with objects that have different collation and code page settings, you must code your queries to consider the rules of collation precedence.
When selecting a collation for your SQL Server installation, consider the following recommendations, presented in priority order:
- Select a binary collation if binary ordering is acceptable.
- If your applications use both nchar/nvarchar and char/varchar data types and may mix them in comparison, select a Windows collation for consistent comparison across data types.
- If your applications use only nchar/nvarchar data types, there is no difference in behavior between Windows collations and SQL collations.
- If your applications use only char/varchar data types, and you utilize Analysis Services, select a Windows collation.
- If your applications use only char/varchar data types, and you are concerned about string comparisons/LIKE performance, and you are not concerned about the better linguistic comparison semantics offered by Windows collations, and you want the dictionary sort order offered by SQL collations, select a SQL collation.
See Also
Reference
Collation Options and International Support
Collation Settings in Setup