Export (0) Print
Expand All

Collation Settings in Setup

Updated: 15 September 2007

Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. Within your organization, you should develop a standard for collation settings, and apply these settings at the time that you install SQL Server. Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. Select a Microsoft Windows locale to match the collation settings in other instances of SQL Server 2005; or select SQL Collations to match settings with the sort orders in earlier versions of SQL Server.

SQL Server 2005 supports setting collations at the following levels of a SQL Server 2005 instance:

  • Server level
  • Database level
  • Column level
  • Expression level

For more information on collation levels, see Collation Terminology. For more information on rebuilding system databases to specify a new system collation, see How to: Install SQL Server 2005 from the Command Prompt.

Change the default settings for Windows collation only if your installation of SQL Server must match the collation settings used by another instance of SQL Server, or if the collations setting must match the Windows system locale of another computer.

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 (code page 1252).
  • Use Modern_Spanish for all variations of Spanish that use the same character set as U.S. English (code page 1252).
  • Use Arabic for all variations of Arabic, which use the Arabic character set (code page 1256).

Sort Order

Select sort order options to use with the selected collation designator. Binary is the fastest sorting order, and it is case sensitive, but it 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.

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. Binary collations provide the fastest data sorts. For more information, see Windows Collation Sorting Styles and Using Binary Collations.

The SQL Collations option is used for compatibility with earlier versions of SQL Server. Select this option to match settings with SQL Server 2000, SQL Server 7.0, or earlier. For more information, see Using SQL Collations.

The following improvements have been made to improve several collations over previous collation behavior:

  • Supplementary character comparison support for Far East collations
  • Addition of a new Indic_General_90_CI_AS (Unicode only) collation version to support changes to sorting behavior
    ms143508.note(en-US,SQL.90).gifImportant:
    Both of these version updates significantly impact sort and comparison behavior. During upgrade of these collations to Microsoft SQL Server 2005, migration to new versions will not be forced. If users migrate to new collations in order to support supplementary characters, all databases and SQL Server objects will have to be reindexed. Because the reindex process may be time consuming, consider the cost-benefit of supplementary character support. Collations from SQL Server 2000 will be supported in SQL Server 2005 in cases where customers choose not to migrate to new collations.

The following collations have been updated for this release of SQL Server 2005 to use the Windows Server 2003 code-point sorting behavior.

Old collation name New collation name

Japanese

Japanese_901

Chinese

Chinese_PRC_90

Chinese_PRC_Stroke

Chinese_PRC_Stroke_90

Chinese_Taiwan_Bopomofo

Chinese_Taiwan_Bopomofo_90

Chinese_Taiwan_Stroke

Chinese_Taiwan_Stroke_90

Korean

Korean_90

Hindi (deprecated in this release)

Indic_General_90_CI_AS (Unicode only)

1Japanese_Unicode is a collation for backward compatibility with SQL Server 7.0. For more information, see http://support.microsoft.com/kb/302747. Use the Japanese collation to match collation settings with SQL Server 2000. If you do not have to match collation settings with previous SQL Server versions, use Japanese_90, the new Japanese collation in SQL Server 2005.

In Control Panel, find the Microsoft Windows locale name in Regional Options in (Windows 2000) or Regional and Language Options (Windows XP), and then use the following table to find the corresponding collation designator and code page to match collation settings with an existing Windows locale in the table of SQL Server 2005 collations below.

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)1

0x82c

Azeri_Cyrillic_90_CI_AS

1251

Azeri-Latin (Azerbaijan)1

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 S.A.R.)1

0xc04

Chinese_Hong_Kong_Stroke_90_CI_AS

950

Chinese (Macau S.A.R.)2

0x1404

Chinese_PRC_90_CI_AS

950

Chinese (PRC)2

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_CI_AS (Unicode only)

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

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_CI_AS

1252

German (Switzerland)

0x807

Latin1_General_CI_AS

1252

Greek

0x408

Greek_CI_AS

1253

Gujarati (India)3

0x447

Indic_General_90_CI_AS (Unicode only)

Unicode

Hebrew

0x40d

Hebrew_CI_AS

1255

Hindi3

0x0439

Indic_General_90_CI_AS (Unicode only)

Unicode

Hungarian

0x40e

Hungarian_CI_AS

1250

Hungarian (Technical)

0x104e

Hungarian_Technical_CI_AS

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

Kannada (India)3

0x44b

Indic_General_90_CI_AS (Unicode only)

Unicode

Kazakh (Kazakstan)1

0x43f

Kazakh_90_CI_AS

1251

Konkani (India)3

0x457

Indic_General_90_CI_AS (Unicode only)

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_CI_AS (Unicode only)

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)3

0x446

Indic_General_90_CI_AS (Unicode only)

Unicode

Romanian

0x418

Romanian_CI_AS

1250

Russian

0x419

Cyrillic_General_CI_AS

1251

Sanskrit (India)3

0x44f

Indic_General_90_CI_AS (Unicode only)

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

Modern_Spanish_CI_AS

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)1

0x45a

Syriac_90_CI_AS (Unicode only)

Unicode

Tamil (India)1

0x449

Indic_General_90_CI_AS (Unicode only)

Unicode

Tatar (Tatarstan)1

0x444

Tatar_90_CI_AS

1251

Telugu (India)1

0x44a

Indic_General_90_CI_AS (Unicode only)

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)1

0x443

Uzbek_Latin_90_CI_AS

1254

Uzbek-Cyrillic (Republic of Uzbeckistan)

0x843

Cyrillic_General_CI_AS

1251

Vietnamese

0x42a

Vietnamese_CI_AS

1258

1New collations for SQL Server 2005; these collations use the Windows 2003 sorting tables and, therefore, support supplementary characters. Both BIN and BIN2 binary sorts are supported for the new 90 Windows collations.

2Use Pingyin or Pronunciation for sorting.

3Major change to sorting behavior; use the new default collation, Indic_General_90_CI_AS (Unicode only).

4 The Hindi collation is deprecated in SQL Server 2005 because the Windows 2000 sorting table is used in this SQL Server release. The collation still exists in the server, but it will not be supported in a future SQL Server release, and it does not show up in ::fn_helpcollations().

5 The Hindi and Lithuanian_Classic collations are deprecated in SQL Server 2005. These collations still exist in the server, but they will not be supported in a future SQL Server release, and they do not show up in ::fn_helpcollations().

Release History

15 September 2007

Changed content:
  • Revised the recommendations for use of Japanese, Japanese_Unicode, and Japanese_90 collations.

5 December 2005

Changed content:
  • Documented support for Macedonian_FYROM_90 collation.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft