COLLATE (SQL Server Compact)
A 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.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 sensitivity. SQL Server Compact supports only the case-insensitive (CI) option through COLLATE clause.AccentSensitivity
Specifies accent-sensitive (AS). SQL Server Compact supports this option only.
The following table lists the collations available to the COLLATE clause in SQL Server Compact.
Windows System Locale |
LCID (Locale ID) |
SQL Collation Name |
Code page |
|---|---|---|---|
Albanian |
0x41c |
Albanian_CI_AS |
1250 |
Arabic (Saudi Arabia) |
0x401 |
Arabic_CI_AS |
1256 |
Chinese (PRC) |
0x804 |
Chinese_PRC_CI_AS |
936 |
Chinese (PRC) |
0x20804 |
Chinese_PRC_Stroke_CI_AS |
936 |
Chinese (Taiwan) |
0x30404 |
Chinese_Taiwan_Bopomofo_CI_AS |
950 |
Chinese (Taiwan) |
0x404 |
Chinese_Taiwan_Stroke_CI_AS |
950 |
Croatian |
0x41a |
Croatian_CI_AS |
1250 |
Russian |
0x419 |
Cyrillic_General_CI_AS |
1251 |
Czech |
0x405 |
Czech_CI_AS |
1250 |
Danish |
0x406 |
Danish_Norwegian_CI_AS |
1252 |
Estonian |
0x425 |
Estonian_CI_AS |
1257 |
Finnish |
0x40b |
Finnish_Swedish_CI_AS |
1252 |
French (France) |
0x40c |
French_CI_AS |
1252 |
German (Phone Book Sort) |
0x10407 |
German_PhoneBook_CI_AS |
1252 |
Greek |
0x408 |
Greek_CI_AS |
1253 |
Hebrew |
0x40d |
Hebrew_CI_AS |
1255 |
Hungarian |
0x40e |
Hungarian_CI_AS |
1250 |
Hungarian (Technical) |
0x1040e |
Hungarian_Technical_CI_AS |
1250 |
Icelandic |
0x40f |
Icelandic_CI_AS |
1252 |
Japanese |
0x411 |
Japanese_CI_AS |
932 |
Korean (Extended Wansung) |
0x412 |
Korean_Wansung_CI_AS |
949 |
Galacian (English - U.S.A.) |
0x409 |
Latin1_General_CI_AS |
1252 |
Latvian |
0x426 |
Latvian_CI_AS |
1257 |
Lithuanian |
0x427 |
Lithuanian_CI_AS |
1257 |
Spanish (International Sort) |
0xc0a |
Modern_Spanish_CI_AS |
1252 |
Polish |
0x415 |
Polish_CI_AS |
1250 |
Romanian |
0x418 |
Romanian_CI_AS |
1250 |
Slovak |
0x41b |
Slovak_CI_AS |
1250 |
Slovenian |
0x424 |
Slovenian_CI_AS |
1250 |
Thai |
0x41e |
Thai_CI_AS |
874 |
Spanish (Traditional Sort) |
0x40a |
Traditional_Spanish_CI_AS |
1252 |
Turkish |
0x41f |
Turkish_CI_AS |
1254 |
Ukrainian |
0x422 |
Ukrainian_CI_AS |
1251 |
Vietnamese |
0x42a |
Vietnamese_CI_AS |
1258 |
Remarks
All databases that are created without specifying the Windows collation name are assigned Latin1_General, the default collation, for 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 '<enterStrongPasswordHere>' COLLATE Traditional_Spanish_CI_AS
For more information about the supported collations in the SP1 release of SQL Server Compact, see Supported Collations (SQL Server Compact). Note that only case-insensitive collations are supported through the COLLATE clause.