Using SQL Server Collations
SQL Server 2008 collation settings depend on the type of installation. Typically, you should choose a SQL Server collation that supports the Windows system locale most commonly used at your organization. To identify your Windows system locale on computers that are running Windows 2000, click Regional Settings in Control Panel, and then click Advanced. In Windows Vista, use the Formats tab. To identify your Windows system locale on computers that are running Windows Server 2003, click Regional and Language Options in Control Panel, and then click the Regional Options tab. In most cases, a computer runs the Windows system locale that matches the language requirements of the user. Therefore, SQL Server Setup automatically detects the Windows system locale and selects the appropriate SQL Server collation. For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.
SQL Server 2008 collations control the following:
The code page that is used to store non-Unicode data in SQL Server.
The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be different for Unicode and non-Unicode characters.
Use the following table to determine which ,if any, collation you should choose.
Note
If you upgrade from an earlier version of a SQL Server collation setting, no collation choice is required.
Task |
Collation |
|---|---|
Install on a new system with no requirements for synchronizing with any existing system. |
Use the collation that is 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 Dictionary order, case-insensitive, for use with 1252 character set SQL Server 2008 collation. To select the equivalent Windows collation, select Collation designator, select Latin1_General, and then clear case-sensitive. |
Upgrade an installation of SQL Server 6.5 or SQL Server 7.0 to a default instance of SQL Server, or to install a default instance of SQL Server 2008 that will version-switch with an installation of SQL Server 6.5. |
Use the SQL Server 2008 collation that Setup selects. |
Synchronize with an existing instance of SQL Server. |
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.It consists of the collation designator name followed by a description of the binary, case, accent, kana, and width-sensitivity options that are specified. Select the same Windows collation designator and sorting options in SQL Server Setup. For more information, 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. Then, use the sort ID to select a SQL Server collation to make your instance of SQL Server compatible with an existing installation. For more information, see Selecting a SQL Server Collation. |
Synchronize with the Windows system locale of another computer. |
In Control Panel, find the locale name under Regional Options (Windows 2000, or Windows Server 2003), or Regional and Language Options (Windows XP), and then use the table that is provided in Collation Settings in Setup. Set the sorting options as described in Windows Collation Sorting Styles. |
Note
When you perform an action that depends on collations, the SQL Server collation that is used by the referenced object must use a code page that is supported by the operating system.