Using SQL Collations
Microsoft SQL Server collation settings depend on the type of installation. In general, choose a SQL Server collation that supports the Microsoft Windows system locale most commonly used at your site. To identify your Windows system locale on computers running Microsoft Windows 2000, click Regional Settings in Control Panel, and then click the General tab if necessary to display the current system locale. To identify your Windows system locale on computers running Windows Server 2003, click Regional and Language Options in Control Panel, and then click the Regional Options tab to display the current system locale. In most cases, a computer runs the Windows system locale that matches the language requirements of the user, so Microsoft SQL Server Setup automatically detects the Windows system locale and chooses the appropriate collation.
SQL Server collations control:
- The code page used for storing non-Unicode data in SQL Server.
- The rules governing how SQL Server sorts and compares characters stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed and automatically select the collation for your SQL Server instance. Sorts and comparisons may be different for Unicode and non-Unicode characters.
Choose a SQL Server collation if:
- You use the replication feature with existing instances of SQL Server version 6.5, SQL Server version 7.0, or SQL Server 2000.
- Your application code depends on the behaviors of previous SQL Server collations.
Use the following table to determine if you need to make a collation choice, and if so, which collation you should choose.
Note
Upgrading SQL Server 7.0 or SQL Server 2000 to SQL Server 2005 keeps the previous SQL Server collation settings; no collation choice is required.
| Task | Collation |
|---|---|
Install on a new system with no requirements for synchronizing with any existing system |
Use the collation identified by Setup, and then choose the desired binary, case, or other option. When SQL Server Setup detects that the computer is running the U.S. English system locale, Setup automatically selects the SQL Server collation: Dictionary order, case-insensitive, for use with 1252 character set. To select the equivalent Windows collation, select Collation designator, select Latin1_General, clear case-sensitive, and select accent-sensitive. |
Upgrade an installation of SQL Server 6.5 or SQL Server 7.0 to a default instance of SQL Server 2005, or to install a default instance of SQL Server 2005 that will version switch with an installation of SQL Server 6.5 |
Use the SQL Server collation chosen by Setup. |
Synchronize (for example, to replicate) with an existing instance of SQL Server 2005 |
Select SERVERPROPERTY(N'Collation') from the Code Editor on the existing instance, and specify Collation. For more information, see SERVERPROPERTY (Transact-SQL). If the collation name of the existing instance starts with "SQL", select the same SQL Server collation in Setup. If the collation name of the existing instance does not start with "SQL", the collation name refers to a Windows collation name and consists of the collation designator name followed by a description of what binary, case, accent, kana, and width-sensitivity options are specified. Select the same Windows collation designator and sorting options in SQL Server Setup. For more information on collation behaviors, see Windows Collation Sorting Styles. |
Synchronize with an existing installation of SQL Server 6.5, SQL Server 7.0, or SQL Server 2000 |
Run sp_helpsort on the existing system, and then use the sort ID to select a SQL Server collation to make your instance of SQL Server 2005 compatible with an existing installation. For more information, see Selecting a SQL Collation. |
Synchronize with the Windows system locale of another computer |
In Control Panel, find the locale name under Regional Options (Windows 2000, or Windows 2003), or Regional and Language Options (Windows XP), and then use the table provided in the topic Collation Settings in Setup. Set the sorting options, as explained in the Books Online topic Windows Collation Sorting Styles. |
Note
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.
See Also
Reference
Selecting a SQL Collation
Setting Client Code Pages
Other Resources
SQL Collation Name (Transact-SQL)