COLLATE (SQL Server Compact Edition)
Clause that can be applied to a database definition to define the collation.
Syntax
COLLATE < collation_name >
< collation_name > :: =
< Windows_collation_name >
< Windows_collation_name >:: =
CollationDesignator_< ComparisonStyle >
< ComparisonStyle > :: =
CaseSensitivity_AccentSensitivity
Arguments
- <collation_name>
Is the name of the collation to be applied to the database definition. The <collation_name> argument can be only a specified <Windows_collation_name>.
- <Windows_collation_name>
Is the collation name for Windows collation. For a list of Windows collation names supported in Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition), see the "Remarks" section later in this topic.
CollationDesignator
Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:- An alphabet or language whose sorting rules are applied when dictionary sorting is specified.
- A code page used to store non-Unicode character data.
For example, Latin1_General and French are collation designators that use code page 1252. Turkish is a collation designator that uses code page 1254.
- CaseSensitivity
Specifies case-insensitive (CI). SQL Server Compact Edition supports this option only.
- AccentSensitivity
Specifies accent-sensitive (AS). SQL Server Compact Edition supports this option only.
Remarks
All databases that are created without specifying the Windows collation name are assigned Latin1_General, the default collation. Example: Latin1_General_CI_AS. This collation uses the Latin1 General dictionary sorting rules, code page 1252. It is case-insensitive and accent-sensitive.
Use the optional COLLATE clause to specify a collation different from the default. For example, to specify a Spanish collation, use a query such as:
CREATE DATABASE "SpanishDB.sdf" DATABASEPASSWORD '<MyPwd>' COLLATE Traditional_Spanish_CI_AS
The following table lists the supported Windows collation names. Ensure that the locale is supported on the device that the database is created.
Note
Some collation values might not be supported on some devices.
| 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) |
0x82c |
Azeri_Cyrillic_90_CI_AS |
1251 |
Azeri-Latin (Azerbaijan) |
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) |
0x20c04 |
Chinese_Hong_Kong_Stroke_90_CI_AS |
950 |
Chinese (Hong Kong S.A.R.) |
0xc04 |
Chinese_Hong_Kong_Stroke_90_CI_AS |
950 |
Chinese (Macau S.A.R.) |
0x1404 |
Chinese_PRC_90_CI_AS |
950 |
Chinese (PRC) |
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 |
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 |
Galacian (English - U.S.A.) |
0x409 |
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 |
1252 |
German (Switzerland) |
0x807 |
Latin1_General_CI_AS |
1252 |
Greek |
0x408 |
Greek_CI_AS |
1253 |
Gujarati (India) |
0x447 |
Indic_General_90 |
Unicode |
Hebrew |
0x40d |
Hebrew_CI_AS |
1255 |
Hindi |
0x0439 |
Indic_General_90 |
Unicode |
Hungarian |
0x40e |
Hungarian_CI_AS |
1250 |
Hungarian (Technical) |
0x104e |
Hungarian_Technical |
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 |
Japanese (Unicode) |
0x10411 |
Japanese_Unicode |
932 |
Kannada (India) |
0x44b |
Indic_General_90 |
Unicode |
Kazakh (Kazakstan) |
0x43f |
Kazakh_90_CI_AS |
1251 |
Konkani (India) |
0x457 |
Indic_General_90 |
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 |
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) |
0x446 |
Indic_General_90 |
Unicode |
Romanian |
0x418 |
Romanian_CI_AS |
1250 |
Russian |
0x419 |
Cyrillic_General_CI_AS |
1251 |
Sanskrit (India) |
0x44f |
Indic_General_90 |
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 |
Latin1_General_AI_CI |
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) |
0x45a |
Syriac_90 |
Unicode |
Tamil (India) |
0x449 |
Indic_General_90 |
Unicode |
Tatar (Tatarstan) |
0x444 |
Tatar_90_CI_AS |
1251 |
Telugu (India) |
0x44a |
Indic_General_90 |
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) |
0x443 |
Uzbek_Latin_90_CI_AS |
1254 |
Uzbek-Cyrillic (Republic of Uzbeckistan) |
0x843 |
Cyrillic_General_CI_AS |
1251 |
Vietnamese |
0x42a |
Vietnamese_CI_AS |
1258 |