Specifying the Default Collation for an Instance of SQL Server

The default collation for an instance of Microsoft® SQL Server™ 2000 is defined during setup. If you choose the minimal or typical setup options, then Setup installs these collations:

• If you upgrade a default instance of SQL Server version 6.5 or SQL Server version 7.0 to SQL Server 2000, or if you install a default instance of SQL Server 2000 that will be version switched with a default instance of SQL Server version 6.5, SQL Server Setup carries forward the same collation used in the existing instance of SQL Server version 6.5 or SQL Server version 7.0, including obsolescent collations.
  
  
• In all other cases, Setup chooses the Windows collation that supports the Windows locale of the computer on which the instance of SQL Server 2000 is being installed.

Note  The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release.

If you choose the Custom setup option, Setup uses the same logic as in the minimal and typical options to set the collation that is selected when the Character Set / Sort Order / Windows Collation window is displayed. You should not use the selected collation in these cases:

• If the instance will be included in a replication scheme, all instances of SQL Server involved in the replication scheme (Publishers, Subscribers, and Distributors) should use the same code page. You should make sure the collation selected by Setup uses the same code page as the other instances of SQL Server in the replication scheme.
  
  
• If the primary language that the instance must support is different than the Windows locale of the computer on which the instance is being installed.

For a table showing which collation designator to specify for a Windows locale, see Windows Collation Names Table.

During setup, the master, model, tempdb, msdb, and Distribution system databases are assigned the same default collation as the default collation chosen for the instance.

See Also

Collation Options for International Support