Collation Settings in Setup
Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. To avoid this, select a Windows locale or SQL Server collation (SQL_*) to match the collation settings in other instances of SQL Server.
SQL Server 2008 supports setting collations at the following levels:
Server
Database
Column
Expression
For more information, see Collation and International Terminology and How to: Install SQL Server 2008 R2 from the Command Prompt.
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. For more information, see Windows Collation Sorting Styles and Guidelines for Using BIN and BIN2 Collations.
SQL Server Collations (SQL_*)
Select this option to match settings with English-language versions SQL Server 2005 or earlier versions. For more information, see Using SQL Server Collations.
Windows system locale
The default installation settings are determined by the Windows system locale. The server-level collation can either be changed during setup, or by changing the Windows system locale before installation. The default collations are set to the oldest available version that is associated with each specific locale. This is due to backwards compatibility reasons. Therefore, these are not always the recommended collations. To take advantage of the SQL Server 2008 features, change the default installation settings for Windows collations..
To set the Windows locale for operating systems before Windows Vista, in Contol Panel, find the Windows system locale name on the Advanced tab in Regional and Language Options. In Windows Vista, use the Formats tab.
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.
Sort Order
Select sort order options to use with the selected collation designator. Binary is the fastest sorting order and is case-sensitive, but 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.
Default Collations in SQL Server Setup
In Control Panel, find the Windows system locale name on the Advanced tab in Regional and Language Options. In Windows Vista, use the Formats" tab. The following table shows the corresponding collation designator to match collation settings with an existing Windows locale:
Windows locale |
Windows LCID |
SQL LCID |
Default collation |
|---|---|---|---|
Afrikaans (South Africa) |
0x0436 |
0x0409 |
Latin1_General_CI_AS |
Albanian (Albania) |
0x041c |
0x041c |
Albanian_CI_AS |
Alsatian (France) |
0x0484 |
0x0409 |
Latin1_General_CI_AS |
Amharic (Ethiopia) |
0x045e |
0x0409 |
Latin1_General_CI_AS |
Arabic (Algeria) |
0x1401 |
0x0401 |
Arabic_CI_AS |
Arabic (Bahrain) |
0x3c01 |
0x0401 |
Arabic_CI_AS |
Arabic (Egypt) |
0x0c01 |
0x0401 |
Arabic_CI_AS |
Arabic (Iraq) |
0x0801 |
0x0401 |
Arabic_CI_AS |
Arabic (Jordan) |
0x2c01 |
0x0401 |
Arabic_CI_AS |
Arabic (Kuwait) |
0x3401 |
0x0401 |
Arabic_CI_AS |
Arabic (Lebanon) |
0x3001 |
0x0401 |
Arabic_CI_AS |
Arabic (Libya) |
0x1001 |
0x0401 |
Arabic_CI_AS |
Arabic (Morocco) |
0x1801 |
0x0401 |
Arabic_CI_AS |
Arabic (Oman) |
0x2001 |
0x0401 |
Arabic_CI_AS |
Arabic (Qatar) |
0x4001 |
0x0401 |
Arabic_CI_AS |
Arabic (Saudi Arabia) |
0x0401 |
0x0401 |
Arabic_CI_AS |
Arabic (Syria) |
0x2801 |
0x0401 |
Arabic_CI_AS |
Arabic (Tunisia) |
0x1c01 |
0x0401 |
Arabic_CI_AS |
Arabic (U.A.E.) |
0x3801 |
0x0401 |
Arabic_CI_AS |
Arabic (Yemen) |
0x2401 |
0x0401 |
Arabic_CI_AS |
Armenian (Armenia) |
0x042b |
0x0419 |
Latin1_General_CI_AS |
Assamese (India) |
0x044d |
0x044d |
Not available at server level |
Azeri (Azerbaijan, Cyrillic) |
0x082c |
0x082c |
Deprecated, not available at server level |
Azeri (Azerbaijan, Latin) |
0x042c |
0x042c |
Deprecated, not available at server level |
Bashkir (Russia) |
0x046d |
0x046d |
Latin1_General_CI_AI |
Basque (Basque) |
0x042d |
0x0409 |
Latin1_General_CI_AS |
Belarusian (Belarus) |
0x0423 |
0x0419 |
Cyrillic_General_CI_AS |
Bengali (Bangladesh) |
0x0845 |
0x0445 |
Not available at server level |
Bengali (India) |
0x0445 |
0x0439 |
Not available at server level |
Bosnian (Bosnia and Herzegovina, Cyrillic) |
0x201a |
0x201a |
Latin1_General_CI_AI |
Bosnian (Bosnia and Herzegovina, Latin) |
0x141a |
0x141a |
Latin1_General_CI_AI |
Breton (France) |
0x047e |
0x047e |
Latin1_General_CI_AI |
Bulgarian (Bulgaria) |
0x0402 |
0x0419 |
Cyrillic_General_CI_AS |
Catalan (Catalan) |
0x0403 |
0x0409 |
Latin1_General_CI_AS |
Chinese (Hong Kong SAR, PRC) |
0x0c04 |
0x0404 |
Chinese_Taiwan_Stroke_CI_AS |
Chinese (Macao SAR) |
0x1404 |
0x1404 |
Latin1_General_CI_AI |
Chinese (Macau) |
*0x21404 |
0x21404 |
Latin1_General_CI_AI |
Chinese (PRC) |
0x0804 |
0x0804 |
Chinese_PRC_CI_AS |
Chinese (PRC) |
*0x20804 |
0x20804 |
Chinese_PRC_Stroke_CI_AS |
Chinese (Singapore) |
0x1004 |
0x0804 |
Chinese_PRC_CI_AS |
Chinese (Singapore) |
*0x21004 |
0x20804 |
Chinese_PRC_Stroke_CI_AS |
Chinese (Taiwan) |
*0x30404 |
0x30404 |
Chinese_Taiwan_Bopomofo_CI_AS |
Chinese (Taiwan) |
0x0404 |
0x0404 |
Chinese_Taiwan_Stroke_CI_AS |
Corsican (France) |
0x0483 |
0x0483 |
Latin1_General_CI_AI |
Croatian (Bosnia and Herzegovina, Latin) |
0x101a |
0x041a |
Croatian_CI_AS |
Croatian (Croatia) |
0x041a |
0x041a |
Croatian_CI_AS |
Czech (Czech Republic) |
0x0405 |
0x0405 |
Czech_CI_AS |
Danish (Denmark) |
0x0406 |
0x0406 |
Danish_Norwegian_CI_AS |
Dari (Afghanistan) |
0x048c |
0x048c |
Latin1_General_CI_AI |
Divehi (Maldives) |
0x0465 |
0x0465 |
Not available at server level |
Dutch (Belgium) |
0x0813 |
0x0409 |
Latin1_General_CI_AS |
Dutch (Netherlands) |
0x0413 |
0x0409 |
Latin1_General_CI_AS |
English (Australia) |
0x0c09 |
0x0409 |
Latin1_General_CI_AS |
English (Belize) |
0x2809 |
0x0409 |
Latin1_General_CI_AS |
English (Canada) |
0x1009 |
0x0409 |
Latin1_General_CI_AS |
English (Caribbean) |
0x2409 |
0x0409 |
Latin1_General_CI_AS |
English (India) |
0x4009 |
0x0409 |
Latin1_General_CI_AS |
English (Ireland) |
0x1809 |
0x0409 |
Latin1_General_CI_AS |
English (Jamaica) |
0x2009 |
0x0409 |
Latin1_General_CI_AS |
English (Malaysia) |
0x4409 |
0x0409 |
Latin1_General_CI_AS |
English (New Zealand) |
0x1409 |
0x0409 |
Latin1_General_CI_AS |
English (Philippines) |
0x3409 |
0x0409 |
Latin1_General_CI_AS |
English (Singapore) |
0x4809 |
0x0409 |
Latin1_General_CI_AS |
English (South Africa) |
0x1c09 |
0x0409 |
Latin1_General_CI_AS |
English (Trinidad and Tobago) |
0x2c09 |
0x0409 |
Latin1_General_CI_AS |
English (United Kingdom) |
0x0809 |
0x0409 |
Latin1_General_CI_AS |
English (United States) |
0x0409 |
0x0409 |
SQL_Latin1_General_CP1_CI_AS |
English (Zimbabwe) |
0x3009 |
0x0409 |
Latin1_General_CI_AS |
Estonian (Estonia) |
0x0425 |
0x0425 |
Estonian_CI_AS |
Faroese (Faroe Islands) |
0x0438 |
0x0409 |
Latin1_General_CI_AS |
Filipino (Philippines) |
0x0464 |
0x0409 |
Latin1_General_CI_AS |
Finnish (Finland) |
0x040b |
0x040b |
Finnish_Swedish_CI_AS |
French (Belgium) |
0x080c |
0x040c |
French_CI_AS |
French (Canada) |
0x0c0c |
0x040c |
French_CI_AS |
French (France) |
0x040c |
0x040c |
French_CI_AS |
French (Luxembourg) |
0x140c |
0x040c |
French_CI_AS |
French (Monaco) |
0x180c |
0x040c |
French_CI_AS |
French (Switzerland) |
0x100c |
0x040c |
French_CI_AS |
Frisian (Netherlands) |
0x0462 |
0x0462 |
Latin1_General_CI_AI |
Galician (Spain) |
0x0456 |
0x0409 |
Latin1_General_CI_AS |
Georgian (Georgia) |
*0x10437 |
0x10437 |
Georgian_Modern_Sort_CI_AS |
Georgian (Georgia) |
0x0437 |
0x0419 |
Latin1_General_CI_AS |
German - Phone Book Sort (DIN) |
*0x10407 |
0x10407 |
German_PhoneBook_CI_AS |
German (Austria) |
0x0c07 |
0x0409 |
Latin1_General_CI_AS |
German (Germany) |
0x0407 |
0x0409 |
Latin1_General_CI_AS |
German (Liechtenstein) |
0x1407 |
0x0409 |
Latin1_General_CI_AS |
German (Luxembourg) |
0x1007 |
0x0409 |
Latin1_General_CI_AS |
German (Switzerland) |
0x0807 |
0x0409 |
Latin1_General_CI_AS |
Greek (Greece) |
0x0408 |
0x0408 |
Greek_CI_AS |
Greenlandic (Greenland) |
0x046f |
0x0406 |
Danish_Norwegian_CI_AS |
Gujarati (India) |
0x0447 |
0x0439 |
Not available at server level |
Hausa (Nigeria, Latin) |
0x0468 |
0x0409 |
Latin1_General_CI_AS |
Hebrew (Israel) |
0x040d |
0x040d |
Hebrew_CI_AS |
Hindi (India) |
0x0439 |
0x0439 |
Not available at server level |
Hungarian (Hungary) |
0x040e |
0x040e |
Hungarian_CI_AS |
Hungarian Technical Sort |
*0x1040e |
0x1040e |
Hungarian_Technical_CI_AS |
Icelandic (Iceland) |
0x040f |
0x040f |
Icelandic_CI_AS |
Igbo (Nigeria) |
0x0470 |
0x0409 |
Latin1_General_CI_AS |
Indonesian (Indonesia) |
0x0421 |
0x0409 |
Latin1_General_CI_AS |
Inuktitut (Canada, Latin) |
0x085d |
0x0409 |
Latin1_General_CI_AS |
Inuktitut (Syllabics) Canada |
0x045d |
0x045d |
Latin1_General_CI_AI |
Irish (Ireland) |
0x083c |
0x0409 |
Latin1_General_CI_AS |
Italian (Italy) |
0x0410 |
0x0409 |
Latin1_General_CI_AS |
Italian (Switzerland) |
0x0810 |
0x0409 |
Latin1_General_CI_AS |
Japanese (Japan XJIS) |
0x0411 |
0x0411 |
Japanese_CI_AS |
Japanese (Japan) |
*0x040411 |
0x40411 |
Latin1_General_CI_AI |
Kannada (India) |
0x044b |
0x0439 |
Not available at server level |
Kazakh (Kazakhstan) |
0x043f |
0x043f |
Kazakh_90_CI_AS |
Khmer (Cambodia) |
0x0453 |
0x0453 |
Not available at server level |
K'iche (Guatemala) |
0x0486 |
0x0c0a |
Modern_Spanish_CI_AS |
Kinyarwanda (Rwanda) |
0x0487 |
0x0409 |
Latin1_General_CI_AS |
Konkani (India) |
0x0457 |
0x0439 |
Not available at server level |
Korean (Korea Dictionary Sort) |
0x0412 |
0x0412 |
Korean_Wansung_CI_AS |
Kyrgyz (Kyrgyzstan) |
0x0440 |
0x0419 |
Cyrillic_General_CI_AS |
Lao (Lao PDR) |
0x0454 |
0x0454 |
Not available at server level |
Latvian (Latvia) |
0x0426 |
0x0426 |
Latvian_CI_AS |
Lithuanian (Lithuania) |
0x0427 |
0x0427 |
Lithuanian_CI_AS |
Lower Sorbian (Germany) |
0x082e |
0x0409 |
Latin1_General_CI_AS |
Luxembourgish (Luxembourg) |
0x046e |
0x0409 |
Latin1_General_CI_AS |
Macedonian (Macedonia, FYROM) |
0x042f |
0x042f |
Macedonian_FYROM_90_CI_AS |
Malay (Brunei Darussalam) |
0x083e |
0x0409 |
Latin1_General_CI_AS |
Malay (Malaysia) |
0x043e |
0x0409 |
Latin1_General_CI_AS |
Malayalam (India) |
0x044c |
0x0439 |
Not available at server level |
Maltese (Malta) |
0x043a |
0x043a |
Latin1_General_CI_AI |
Maori (New Zealand) |
0x0481 |
0x0481 |
Latin1_General_CI_AI |
Mapudungun (Chile) |
0x047a |
0x047a |
Latin1_General_CI_AI |
Marathi (India) |
0x044e |
0x0439 |
Not available at server level |
Mohawk (Canada) |
0x047c |
0x047c |
Latin1_General_CI_AI |
Mongolian (Mongolia) |
0x0450 |
0x0419 |
Cyrillic_General_CI_AS |
Mongolian (PRC) |
0x0850 |
0x0419 |
Cyrillic_General_CI_AS |
Nepali (Nepal) |
0x0461 |
0x0461 |
Not available at server level |
Norwegian (Bokmål, Norway) |
0x0414 |
0x0414 |
Latin1_General_CI_AI |
Norwegian (Nynorsk, Norway) |
0x0814 |
0x0414 |
Latin1_General_CI_AI |
Occitan (France) |
0x0482 |
0x040c |
French_CI_AS |
Oriya (India) |
0x0448 |
0x0439 |
Not available at server level |
Pashto (Afghanistan) |
0x0463 |
0x0463 |
Not available at server level |
Persian (Iran) |
0x0429 |
0x0429 |
Latin1_General_CI_AI |
Polish (Poland) |
0x0415 |
0x0415 |
Polish_CI_AS |
Portuguese (Brazil) |
0x0416 |
0x0409 |
Latin1_General_CI_AS |
Portuguese (Portugal) |
0x0816 |
0x0409 |
Latin1_General_CI_AS |
Punjabi (India) |
0x0446 |
0x0439 |
Not available at server level |
Quechua (Bolivia) |
0x046b |
0x0409 |
Latin1_General_CI_AS |
Quechua (Ecuador) |
0x086b |
0x0409 |
Latin1_General_CI_AS |
Quechua (Peru) |
0x0c6b |
0x0409 |
Latin1_General_CI_AS |
Romanian (Romania) |
0x0418 |
0x0418 |
Romanian_CI_AS |
Romansh (Switzerland) |
0x0417 |
0x0417 |
Latin1_General_CI_AI |
Russian (Russia) |
0x0419 |
0x0419 |
Cyrillic_General_CI_AS |
Sami (Inari, Finland) |
0x243b |
0x083b |
Latin1_General_CI_AI |
Sami (Lule, Norway) |
0x103b |
0x043b |
Latin1_General_CI_AI |
Sami (Lule, Sweden) |
0x143b |
0x083b |
Latin1_General_CI_AI |
Sami (Northern, Finland) |
0x0c3b |
0x083b |
Latin1_General_CI_AI |
Sami (Northern, Norway) |
0x043b |
0x043b |
Latin1_General_CI_AI |
Sami (Northern, Sweden) |
0x083b |
0x083b |
Latin1_General_CI_AI |
Sami (Skolt, Finland) |
0x203b |
0x083b |
Latin1_General_CI_AI |
Sami (Southern, Norway) |
0x183b |
0x043b |
Latin1_General_CI_AI |
Sami (Southern, Sweden) |
0x1c3b |
0x083b |
Latin1_General_CI_AI |
Sanskrit (India) |
0x044f |
0x0439 |
Not available at server level |
Serbian (Bosnia and Herzegovina, Cyrillic) |
0x1c1a |
0x0c1a |
Latin1_General_CI_AI |
Serbian (Bosnia and Herzegovina, Latin) |
0x181a |
0x081a |
Latin1_General_CI_AI |
Serbian (Serbia, Cyrillic) |
0x0c1a |
0x0c1a |
Latin1_General_CI_AI |
Serbian (Serbia, Latin) |
0x081a |
0x081a |
Latin1_General_CI_AI |
Sesotho sa Leboa/Northern Sotho (South Africa) |
0x046c |
0x0409 |
Latin1_General_CI_AS |
Setswana/Tswana (South Africa) |
0x0432 |
0x0409 |
Latin1_General_CI_AS |
Sinhala (Sri Lanka) |
0x045b |
0x0439 |
Not available at server level |
Slovak (Slovakia) |
0x041b |
0x041b |
Slovak_CI_AS |
Slovenian (Slovenia) |
0x0424 |
0x0424 |
Slovenian_CI_AS |
Spanish (Argentina) |
0x2c0a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Bolivia) |
0x400a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Chile) |
0x340a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Colombia) |
0x240a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Costa Rica) |
0x140a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Dominican Republic) |
0x1c0a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Ecuador) |
0x300a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (El Salvador) |
0x440a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Guatemala) |
0x100a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Honduras) |
0x480a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Mexico) |
0x080a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Nicaragua) |
0x4c0a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Panama) |
0x180a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Paraguay) |
0x3c0a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Peru) |
0x280a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Puerto Rico) |
0x500a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Spain) |
0x0c0a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Spain, Traditional Sort) |
0x040a |
0x040a |
Traditional_Spanish_CI_AS |
Spanish (United States) |
0x540a |
0x0409 |
Latin1_General_CI_AS |
Spanish (Uruguay) |
0x380a |
0x0c0a |
Modern_Spanish_CI_AS |
Spanish (Venezuela) |
0x200a |
0x0c0a |
Modern_Spanish_CI_AS |
Swahili (Kenya) |
0x0441 |
0x0409 |
Latin1_General_CI_AS |
Swedish (Finland) |
0x081d |
0x040b |
Finnish_Swedish_CI_AS |
Swedish (Sweden) |
0x041d |
0x040b |
Finnish_Swedish_CI_AS |
Syriac (Syria) |
0x045a |
0x045a |
Not available at server level |
Tajik (Tajikistan) |
0x0428 |
0x0419 |
Cyrillic_General_CI_AS |
Tamazight (Algeria, Latin) |
0x085f |
0x085f |
Latin1_General_CI_AI |
Tamil (India) |
0x0449 |
0x0439 |
Not available at server level |
Tatar (Russia) |
0x0444 |
0x0444 |
Cyrillic_General_CI_AS |
Telugu (India) |
0x044a |
0x0439 |
Not available at server level |
Thai (Thailand) |
0x041e |
0x041e |
Thai_CI_AS |
Tibetan (PRC) |
0x0451 |
0x0451 |
Not available at server level |
Turkish (Turkey) |
0x041f |
0x041f |
Turkish_CI_AS |
Turkmen (Turkmenistan) |
0x0442 |
0x0442 |
Latin1_General_CI_AI |
Uighur (PRC) |
0x0480 |
0x0480 |
Latin1_General_CI_AI |
Ukrainian (Ukraine) |
0x0422 |
0x0422 |
Ukrainian_CI_AS |
Upper Sorbian (Germany) |
0x042e |
0x042e |
Latin1_General_CI_AI |
Urdu (Pakistan) |
0x0420 |
0x0420 |
Latin1_General_CI_AI |
Uzbek (Uzbekistan, Cyrillic) |
0x0843 |
0x0419 |
Cyrillic_General_CI_AS |
Uzbek (Uzbekistan, Latin) |
0x0443 |
0x0443 |
Uzbek_Latin_90_CI_AS |
Vietnamese (Vietnam) |
0x042a |
0x042a |
Vietnamese_CI_AS |
Welsh (United Kingdom) |
0x0452 |
0x0452 |
Latin1_General_CI_AI |
Wolof (Senegal) |
0x0488 |
0x040c |
French_CI_AS |
Xhosa/isiXhosa (South Africa) |
0x0434 |
0x0409 |
Latin1_General_CI_AS |
Yakut (Russia) |
0x0485 |
0x0485 |
Latin1_General_CI_AI |
Yi (PRC) |
0x0478 |
0x0409 |
Latin1_General_CI_AS |
Yoruba (Nigeria) |
0x046a |
0x0409 |
Latin1_General_CI_AS |
Zulu/isiZulu (South Africa) |
0x0435 |
0x0409 |
Latin1_General_CI_AS |
See Also