Share via

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 3.5 supports only the case-insensitive (CI) option through COLLATE clause.
  • AccentSensitivity
    Specifies accent-sensitive (AS). SQL Server Compact 3.5 supports this option only.

The following table lists the collations available to the COLLATE clause in SQL Server Compact 3.5.

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 3.5, see Supported Collations (SQL Server Compact). Note that only case-insensitive collations are supported through the COLLATE clause.

See Also

Other Resources

International Considerations (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)