COLLATE (SQL Server Compact)

1 out of 1 rated this helpful - Rate this topic

SQL Server 2005

A clause that can be applied to a database definition to define the collation.


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

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.

Concepts

International Considerations (SQL Server Compact)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)