Collation Settings in Setup
Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. Within your organization, you should develop a standard for collation settings, and apply these settings at the time that you install SQL Server. Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. Select a Microsoft Windows locale to match the collation settings in other instances of SQL Server 2005; or select SQL Collations to match settings with the sort orders in earlier versions of SQL Server.
SQL Server 2005 supports setting collations at the following levels of a SQL Server 2005 instance:
- Server level
- Database level
- Column level
- Expression level
For more information on collation levels, see Collation Terminology. For more information on rebuilding system databases to specify a new system collation, see How to: Install SQL Server 2005 from the Command Prompt.
Windows system locale
Change the default settings for Windows collation only if your installation of SQL Server must match the collation settings used by another instance of SQL Server, or if the collations setting must match the Windows system locale of another computer.
Collation Designator
Select the name of a specific Windows collation from the Collation Designator list. For example:
- Use Latin1_General for the U.S. English character set (code page 1252).
- Use Modern_Spanish for all variations of Spanish that use the same character set as U.S. English (code page 1252).
- Use Arabic for all variations of Arabic, which use the Arabic character set (code page 1256).
Sort Order
Select sort order options to use with the selected collation designator. Binary is the fastest sorting order, and it is case sensitive, but it can yield unexpected sort orders. 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.
Binary Collations
Binary collations sort and compare data in SQL Server based on the bit pattern for each character. Each binary collation in SQL Server maps to a specific language locale and ANSI code page, and each performs case-sensitive and accent-sensitive data sorts. Binary collations provide the fastest data sorts. For more information, see Windows Collation Sorting Styles and Using Binary Collations.
SQL Collations
The SQL Collations option is used for compatibility with earlier versions of SQL Server. Select this option to match settings with SQL Server 2000, SQL Server 7.0, or earlier. For more information, see Using SQL Collations.
New Collation Versions
The following improvements have been made to improve several collations over previous collation behavior:
Supplementary character comparison support for Far East collations
Addition of a new Indic_General_90_CI_AS (Unicode only) collation version to support changes to sorting behavior
Important
Both of these version updates significantly impact sort and comparison behavior. During upgrade of these collations to Microsoft SQL Server 2005, migration to new versions will not be forced. If users migrate to new collations in order to support supplementary characters, all databases and SQL Server objects will have to be reindexed. Because the reindex process may be time consuming, consider the cost-benefit of supplementary character support. Collations from SQL Server 2000 will be supported in SQL Server 2005 in cases where customers choose not to migrate to new collations.
The following collations have been updated for this release of SQL Server 2005 to use the Windows Server 2003 code-point sorting behavior.
| Old collation name | New collation name |
|---|---|
Japanese |
Japanese_901 |
Chinese |
Chinese_PRC_90 |
Chinese_PRC_Stroke |
Chinese_PRC_Stroke_90 |
Chinese_Taiwan_Bopomofo |
Chinese_Taiwan_Bopomofo_90 |
Chinese_Taiwan_Stroke |
Chinese_Taiwan_Stroke_90 |
Korean |
Korean_90 |
Hindi (deprecated in this release) |
Indic_General_90_CI_AS (Unicode only) |
1Japanese_Unicode is a collation for backward compatibility with SQL Server 7.0. For more information, see https://support.microsoft.com/kb/302747. Use the Japanese collation to match collation settings with SQL Server 2000. If you do not have to match collation settings with previous SQL Server versions, use Japanese_90, the new Japanese collation in SQL Server 2005.
Default Collations in SQL Server Setup
In Control Panel, find the Microsoft Windows locale name in Regional Options in (Windows 2000) or Regional and Language Options (Windows XP), and then use the following table to find the corresponding collation designator and code page to match collation settings with an existing Windows locale in the table of SQL Server 2005 collations below.
| Windows System Locale | LCID (Locale ID) | Default SQL Collation | Code page |
|---|---|---|---|
Afrikaans |
0x436 |
Latin1_General_CI_AS |
1252 |
Albanian |
0x41c |
Albanian_CI_AS |
1250 |
Arabic (Algeria) |
0x1401 |
Arabic_CI_AS |
1256 |
Arabic (Bahrain) |
0x3c01 |
Arabic_CI_AS |
1256 |
Arabic (Egypt) |
0xc01 |
Arabic_CI_AS |
1256 |
Arabic (Iraq) |
0x801 |
Arabic_CI_AS |
1256 |
Arabic (Jordan) |
0x2c01 |
Arabic_CI_AS |
1256 |
Arabic (Kuwait) |
0x3401 |
Arabic_CI_AS |
1256 |
Arabic (Lebanon) |
0x3001 |
Arabic_CI_AS |
1256 |
Arabic (Libya) |
0x1001 |
Arabic_CI_AS |
1256 |
Arabic (Morocco) |
0x1801 |
Arabic_CI_AS |
1256 |
Arabic (Oman) |
0x2001 |
Arabic_CI_AS |
1256 |
Arabic (Qatar) |
0x4001 |
Arabic_CI_AS |
1256 |
Arabic (Saudi Arabia) |
0x401 |
Arabic_CI_AS |
1256 |
Arabic (Syria) |
0x2801 |
Arabic_CI_AS |
1256 |
Arabic (Tunisia) |
0x1c01 |
Arabic_CI_AS |
1256 |
Arabic (U.A.E.) |
0x3801 |
Arabic_CI_AS |
1256 |
Arabic (Yemen) |
0x2401 |
Arabic_CI_AS |
1256 |
Armenian (Republic of Armenia) |
0x42b |
Latin1_General_CI_AS |
Unicode |
Azeri-Cyrillic (Azerbaijan)1 |
0x82c |
Azeri_Cyrillic_90_CI_AS |
1251 |
Azeri-Latin (Azerbaijan)1 |
0x42c |
Azeri_Latin_90_CI_AS |
1254 |
Basque |
0x42d |
Latin1_General_CI_AS |
1252 |
Belarusian |
0x423 |
Cyrillic_General_CI_AS |
1251 |
Bulgarian |
0x402 |
Cyrillic_General_CI_AS |
1251 |
Catalan |
0x403 |
Latin1_General_CI_AS |
1252 |
Chinese (Hong Kong S.A.R.)1 |
0xc04 |
Chinese_Hong_Kong_Stroke_90_CI_AS |
950 |
Chinese (Macau S.A.R.)2 |
0x1404 |
Chinese_PRC_90_CI_AS |
950 |
Chinese (PRC)2 |
0x804 |
Chinese_PRC_CI_AS |
936 |
Chinese (PRC) |
0x20804 |
Chinese_PRC_Stroke_CI_AS |
936 |
Chinese (Singapore) |
0x1004 |
Chinese_PRC_90_CI_AS |
936 |
Chinese (Taiwan) |
0x404 |
Chinese_Taiwan_Stroke_CI_AS |
950 |
Chinese (Taiwan) |
0x30404 |
Chinese_Taiwan_Bopomofo_CI_AS |
950 |
Croatian |
0x41a |
Croatian_CI_AS |
1250 |
Czech |
0x405 |
Czech_CI_AS |
1250 |
Danish |
0x406 |
Danish_Norwegian_CI_AS |
1252 |
Divehi1 |
0x465 |
Divehi_90_CI_AS (Unicode only) |
Unicode |
Dutch (Belgium) |
0x813 |
Latin1_General_CI_AS |
1252 |
Dutch (Netherlands) |
0x413 |
Latin1_General_CI_AS |
1252 |
English (Australia) |
0xc09 |
Latin1_General_CI_AS |
1252 |
English (Belize) |
0x2809 |
Latin1_General_CI_AS |
1252 |
English (Canada) |
0x1009 |
Latin1_General_CI_AS |
1252 |
English (Caribbean) |
0x2409 |
Latin1_General_CI_AS |
1252 |
English (Hong Kong S.A.R.) |
0x3c09 |
Latin1_General_CI_AS |
1252 |
English (India) |
0x4009 |
Latin1_General_CI_AS |
1252 |
English (Indonesia) |
0x3809 |
Latin1_General_CI_AS |
1252 |
English (Ireland) |
0x1809 |
Latin1_General_CI_AS |
1252 |
English (Jamaica) |
0x2009 |
Latin1_General_CI_AS |
1252 |
English (Malaysia) |
0x4409 |
Latin1_General_CI_AS |
1252 |
English (New Zealand) |
0x1409 |
Latin1_General_CI_AS |
1252 |
English (Philippines) |
0x3409 |
Latin1_General_CI_AS |
1252 |
English (Singapore) |
0x4809 |
Latin1_General_CI_AS |
1252 |
English (South Africa) |
0x1c09 |
Latin1_General_CI_AS |
1252 |
English (Trinidad) |
0x2c09 |
Latin1_General_CI_AS |
1252 |
English (United Kingdom) |
0x809 |
Latin1_General_CI_AS |
1252 |
English (United States) |
0x409 |
SQL_Latin1_General_CP1_CI_AS |
1252 |
English (Zimbabwe) |
0x3009 |
Latin1_General_CI_AS |
1252 |
Estonian |
0x425 |
Estonian_CI_AS |
1257 |
Faeroese |
0x438 |
Latin1_General_CI_AS |
1252 |
Farsi |
0x429 |
Arabic_CI_AS |
1256 |
Finnish |
0x40b |
Finnish_Swedish_CI_AS |
1252 |
French (Belgium) |
0x80c |
French_CI_AS |
1252 |
French (Canada) |
0xc0c |
French_CI_AS |
1252 |
French (France) |
0x40c |
French_CI_AS |
1252 |
French (Luxembourg) |
0x140c |
French_CI_AS |
1252 |
French (Monaco) |
0x180c |
French_CI_AS |
1252 |
French (Switzerland) |
0x100c |
French_CI_AS |
1252 |
Galician (Spain) |
0x456 |
Latin1_General_CI_AS |
1252 |
Georgian |
0x437 |
Latin1_General_CI_AS |
Unicode |
German (Austria) |
0xc07 |
Latin1_General_CI_AS |
1252 |
German (Germany) |
0x407 |
Latin1_General_CI_AS |
1252 |
German (Liechtenstein) |
0x1407 |
Latin1_General_CI_AS |
1252 |
German (Luxembourg) |
0x1007 |
Latin1_General_CI_AS |
1252 |
German (Phone Book Sort) |
0x10407 |
German_PhoneBook_CI_AS |
1252 |
German (Switzerland) |
0x807 |
Latin1_General_CI_AS |
1252 |
Greek |
0x408 |
Greek_CI_AS |
1253 |
Gujarati (India)3 |
0x447 |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Hebrew |
0x40d |
Hebrew_CI_AS |
1255 |
Hindi3 |
0x0439 |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Hungarian |
0x40e |
Hungarian_CI_AS |
1250 |
Hungarian (Technical) |
0x104e |
Hungarian_Technical_CI_AS |
1250 |
Icelandic |
0x40f |
Icelandic_CI_AS |
1252 |
Indonesian |
0x421 |
Latin1_General_CI_AS |
1252 |
Italian (Italy) |
0x410 |
Latin1_General_CI_AS |
1252 |
Italian (Switzerland) |
0x810 |
Latin1_General_CI_AS |
1252 |
Japanese |
0x411 |
Japanese_CI_AS |
932 |
Kannada (India)3 |
0x44b |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Kazakh (Kazakstan)1 |
0x43f |
Kazakh_90_CI_AS |
1251 |
Konkani (India)3 |
0x457 |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Korean (Extended Wansung) |
0x0412 |
Korean_Wansung_CI_AS |
949 |
Kyrgyz-Cyrillic (Republic of Kyrgyztan) |
0x440 |
Cyrillic_General_CI_AS |
1251 |
Latvian |
0x426 |
Latvian_CI_AS |
1257 |
Lithuanian |
0x427 |
Lithuanian_CI_AS |
1257 |
Macedonian (FYROM) |
0x42f |
Macedonian_FYROM_90_CI_AS |
1251 |
Malay (Brunei Darussalam) |
0x83e |
Latin1_General_CI_AS |
1252 |
Malay (Malaysia) |
0x43e |
Latin1_General_CI_AS |
1252 |
Marathi (India)3 |
0x44e |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Mongolian-Cyrillic (Mongolia) |
0x450 |
Cyrillic_General_CI_AS |
1251 |
Norwegian (Bokmal) |
0x414 |
Danish_Norwegian_CI_AS |
1252 |
Norwegian (Nynorsk) |
0x814 |
Danish_Norwegian_CI_AS |
1252 |
Polish |
0x415 |
Polish_CI_AS |
1250 |
Portuguese (Brazil) |
0x416 |
Latin1_General_CI_AS |
1252 |
Portuguese (Portugal) |
0x816 |
Latin1_General_CI_AS |
1252 |
Punjab-Gurmuhki (India)3 |
0x446 |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Romanian |
0x418 |
Romanian_CI_AS |
1250 |
Russian |
0x419 |
Cyrillic_General_CI_AS |
1251 |
Sanskrit (India)3 |
0x44f |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Serbian (Cyrillic) |
0xc1a |
Cyrillic_General_CI_AS |
1251 |
Serbian (Latin) |
0x81a |
Cyrillic_General_CI_AS |
1250 |
Slovak |
0x41b |
Slovak_CI_AS |
1250 |
Slovenian |
0x424 |
Slovenian_CI_AS |
1250 |
Spanish (Argentina) |
0x2c0a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Bolivia) |
0x400a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Chile) |
0x340a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Colombia) |
0x240a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Costa Rica) |
0x140a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Dominican Republic) |
0x1c0a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Ecuador) |
0x300a |
Modern_Spanish_CI_AS |
1252 |
Spanish (El Salvador) |
0x440a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Guatemala) |
0x100a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Honduras) |
0x480a |
Modern_Spanish_CI_AS |
1252 |
Spanish (International Sort) |
0xc0a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Mexico) |
0x80a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Nicaragua) |
0x4c0a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Panama) |
0x180a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Paraguay) |
0x3c0a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Peru) |
0x280a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Puerto Rico) |
0x500a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Traditional Sort) |
0x40a |
Traditional_Spanish_CI_AS |
1252 |
Spanish (Uruguay) |
0x380a |
Modern_Spanish_CI_AS |
1252 |
Spanish (Venezuela) |
0x200a |
Modern_Spanish_CI_AS |
1252 |
Swahili |
0x441 |
Latin1_General_CI_AS |
1252 |
Swedish |
0x41d |
Finnish_Swedish_CI_AS |
1252 |
Swedish (Finland) |
0x81d |
Finnish_Swedish_CI_AS |
1252 |
Syriac (Syria)1 |
0x45a |
Syriac_90_CI_AS (Unicode only) |
Unicode |
Tamil (India)1 |
0x449 |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Tatar (Tatarstan)1 |
0x444 |
Tatar_90_CI_AS |
1251 |
Telugu (India)1 |
0x44a |
Indic_General_90_CI_AS (Unicode only) |
Unicode |
Thai |
0x41e |
Thai_CI_AS |
874 |
Turkish |
0x41f |
Turkish_CI_AS |
1254 |
Ukrainian |
0x422 |
Ukrainian_CI_AS |
1251 |
Urdu |
0x420 |
Arabic_CI_AS |
1256 |
Uzbek (Latin)1 |
0x443 |
Uzbek_Latin_90_CI_AS |
1254 |
Uzbek-Cyrillic (Republic of Uzbeckistan) |
0x843 |
Cyrillic_General_CI_AS |
1251 |
Vietnamese |
0x42a |
Vietnamese_CI_AS |
1258 |
1New collations for SQL Server 2005; these collations use the Windows 2003 sorting tables and, therefore, support supplementary characters. Both BIN and BIN2 binary sorts are supported for the new 90 Windows collations.
2Use Pingyin or Pronunciation for sorting.
3Major change to sorting behavior; use the new default collation, Indic_General_90_CI_AS (Unicode only).
4 The Hindi collation is deprecated in SQL Server 2005 because the Windows 2000 sorting table is used in this SQL Server release. The collation still exists in the server, but it will not be supported in a future SQL Server release, and it does not show up in ::fn_helpcollations().
5 The Hindi and Lithuanian_Classic collations are deprecated in SQL Server 2005. These collations still exist in the server, but they will not be supported in a future SQL Server release, and they do not show up in ::fn_helpcollations().
See Also
Reference
Collation Options and International Support
Collation Settings
Windows Collation Sorting Styles
Concepts
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|
5 December 2005 |
|