Using SQL Collations
SQL collation settings correspond to the type of installation. In general, choose a SQL collation that supports the Windows locale most commonly used at your site. For more information about identifying your site Windows Locale, see Regional Settings in Windows Control Panel. In many cases, a computer will run the Windows locale that matches the language requirements of the user, so Setup automatically detects the Windows locale and chooses the appropriate SQL collation.
SQL collations control:
- The code page used for storing non-Unicode data in Microsoft® SQL Server™.
- The rules governing how SQL Server sorts and compares characters stored in both Unicode and non-Unicode data types.
Choose a SQL collation if:
- You use the replication feature with existing instances of SQL Server version 6.5 or SQL Server version 7.0
- Your application code depends on the behaviors of the previous SQL Server collations.
An upgrade of SQL Server 7.0 to SQL Server 2000 keeps the previous SQL collation settings; no collation choice is required.
Use this table to determine if you need to make a collation choice, and if so, which collation you should choose.
| Installation you want | Collation to choose |
|---|---|
| To install on a new system with no compatibility requirements for synchronizing with any type of existing system | Use the locale identified by Setup, and then choose the desired binary, case, or other options.
For this release of SQL Server, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set. To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive. |
| To upgrade an installation of SQL Server 6.5 or SQL Server 7.0 to a default instance of SQL Server 2000, or to install a default instance of SQL Server 2000 that will version switch with an installation of SQL Server 6.5 | Use the SQL collation chosen by Setup. |
| To synchronize (for example, to replicate) with an existing instance of SQL Server 2000 | Select SERVERPROPERTY(N'Collation') on the existing instance, and specify that collation. If the collation name of the existing instance starts with SQL, select the same SQL 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 Setup. |
| To synchronize with an existing installation of SQL Server 6.5 or SQL Server 7.0 | Execute sp_helpsort on the existing system, and then use the sort ID to select a SQL collation to make your instance of SQL Server 2000 compatible with an existing installation.
For more information, see Selecting a SQL Collation. |
| To synchronize with a Windows locale of another computer | In Control Panel, find the locale name from the Regional Settings application (Microsoft Windows NT® 4.0, Microsoft Windows® 98, and Microsoft Windows 95), or from the Regional Options application (Microsoft Windows 2000), and then use the table provided in the topic Windows Collation Designators. Set the sorting options, as explained in the 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. For more information, see Specifying Collations.