• Posted by Prakash chheatry
  • 9:57 PM
  • 0 comments
  • Labels: No labels

Using SQL Server Collations

SQL Server 2008 R2

  • SQL Server 2008 collation settings depend on the type of installation. Typically, you should choose a SQL Server collation that supports the Windows system locale most commonly used at your organization. To identify your Windows system locale on computers that are running Windows 2000, click Regional Settings in Control Panel, and then click Advanced. In Windows Vista, use the Formats tab. To identify your Windows system locale on computers that are running Windows Server 2003, click Regional and Language Options in Control Panel, and then click the Regional Options tab. In most cases, a computer runs the Windows system locale that matches the language requirements of the user. Therefore, SQL Server Setup automatically detects the Windows system locale and selects the appropriate SQL Server collation. For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.

    SQL Server 2008 collations control the following:

    • The code page that is used to store non-Unicode data in SQL Server.
    • The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be different for Unicode and non-Unicode characters.

    Use the following table to determine which ,if any, collation you should choose.

    Note

    If you upgrade from an earlier version of a SQL Server collation setting, no collation choice is required.

    Task

    Collation

    Install on a new system with no requirements for synchronizing with any existing system.

    Use the collation that is identified by Setup, and then choose the desired binary, case, or other option.

    When SQL Server Setup detects that the computer is running the U.S. English system locale, Setup automatically selects the Dictionary order, case-insensitive, for use with 1252 character set SQL Server 2008 collation.

    To select the equivalent Windows collation, select Collation designator, select Latin1_General, and then clear case-sensitive.

    Upgrade an installation of SQL Server 6.5 or SQL Server 7.0 to a default instance of SQL Server, or to install a default instance of SQL Server 2008 that will version-switch with an installation of SQL Server 6.5.

    Use the SQL Server 2008 collation that Setup selects.

    Synchronize with an existing instance of SQL Server.

    Select SERVERPROPERTY(N'Collation') from the Code Editor on the existing instance, and specify Collation. For more information, see SERVERPROPERTY (Transact-SQL). If the collation name of the existing instance starts with "SQL", select the same SQL Server collation in Setup. If the collation name of the existing instance does not start with "SQL", the collation name refers to a Windows collation name.It consists of the collation designator name followed by a description of the binary, case, accent, kana, and width-sensitivity options that are specified. Select the same Windows collation designator and sorting options in SQL Server Setup.

    For more information, see Windows Collation Sorting Styles.

    Synchronize with an existing installation of SQL Server 6.5, SQL Server 7.0, or SQL Server 2000.

    Run sp_helpsort on the existing system. Then, use the sort ID to select a SQL Server collation to make your instance of SQL Server compatible with an existing installation.

    For more information, see Selecting a SQL Server Collation.

    Synchronize with the Windows system locale of another computer.

    In Control Panel, find the locale name under Regional Options (Windows 2000, or Windows Server 2003), or Regional and Language Options (Windows XP), and then use the table that is provided in Collation Settings in Setup. Set the sorting options as described in Windows Collation Sorting Styles.


     

    The following SQL collations are listed on the Collation Settings page of the SQL Server Installation Wizard.

    SQL sort order ID

    Sort order name

    Description

    SQL collation

    31

    diction.437

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp437_CS_AS_KI_WI

    32

    nocase.437

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp437_CI_AS_KI_WI

    33

    nocasepr.437

    Dictionary order, case-insensitive, uppercase preference

    SQL_Latin1_General_Pref_Cp437_CI_AS_KI_WI

    34

    noaccent.437

    Dictionary order, case-insensitive, accent-insensitive

    SQL_Latin1_General_Cp437_CI_AI_KI_WI

    40

    cp437bin.437

    Binary

    SQL_Latin1_General_Cp437_BIN

    40

    cp850bin.850

    Binary

    SQL_Latin1_General_Cp850_BIN

    41

    diction.850

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp850_CS_AS_KI_WI

    42

    nocase.850

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp850_CI_AS_KI_WI

    44

    noaccent.850

    Dictionary order, case-insensitive, accent-insensitive

    SQL_Latin1_General_Cp850_CI_AI_KI_WI

    44

    nocase34.850

    Dictionary order, case-insensitive, uppercase preference

    SQL_Latin1_General_Pref_Cp850_CI_AS_KI_WI

    49

    nocase34.850

    Strict compatibility with Version 1.x case-insensitive databases

    SQL_1xCompat_Cp850_CI_AS_KI_WI

    51

    diction.iso

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1_CS_AS_KI_WI

    52

    nocase.iso

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1_CI_AS_KI_WI

    53

    nocasepr.iso

    Dictionary order, case-insensitive, uppercase preference

    SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI

    54

    noaccent.iso

    Dictionary order, case-insensitive, accent-insensitive

    SQL_Latin1_General_Cp1_CI_AI_KI_WI

    55

    altdict.850

    Alternate dictionary order, case-sensitive

    SQL_AltDiction_Cp850_CS_AS_KI_WI

    56

    altnocsp.850

    Alternate dictionary order, case-insensitive, uppercase preference

    SQL_AltDiction_Pref_Cp850_CI_AS_KI_WI

    57

    altnoacc.850

    Alternate dictionary order, case-insensitive, accent-insensitive

    SQL_AltDiction_Cp850_CI_AI_KI_WI

    58

    scan.850

    Scandinavian dictionary sort order, case-insensitive, uppercase preference

    SQL_Scandainavian_Pref_Cp850_CI_AS_KI_WI

    59

    scandict.850

    Scandinavian dictionary sort order, case-sensitive

    SQL_Scandainavian_Cp850_CS_AS_KI_WI

    60

    scannocase.850

    Case-insensitive Scandinavian dictionary sort order, without case preference

    SQL_Scandainavian_Cp850_CI_AS_KI_WI

    61

    altnocs.850

    Alternate dictionary order, case-insensitive

    SQL_AltDiction_Cp850_CI_AS_KI_WI

    80

    binary.250

    Binary

    SQL_Latin1_General_1250_BIN

    81

    diction.250

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1250_CS_AS_KI_WI

    82

    nocase.250

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1250_CI_AS_KI_WI

    83

    csydic.250

    Czech dictionary order, case-sensitive

    SQL_Czech_Cp1250_CS_AS_KI_WI

    84

    csync.250

    Czech dictionary order, case-insensitive

    SQL_Czech_Cp1250_CI_AS_KI_WI

    85

    hundic.250

    Hungarian dictionary order, case-sensitive

    SQL_Hungarian_Cp1250_CS_AS_KI_WI

    86

    hunnc.250

    Hungarian dictionary order, case-insensitive

    SQL_Hungarian_Cp1250_CI_AS_KI_WI

    87

    plkdic.250

    Polish dictionary order, case-sensitive

    SQL_Polish_Cp1250_CS_AS_KI_WI

    88

    plknc.250

    Polish dictionary order, case-insensitive

    SQL_Polish_Cp1250_CI_AS_KI_WI

    89

    romdic.250

    Romanian dictionary order, case-sensitive

    SQL_Romanian_Cp1250_CS_AS_KI_WI

    90

    romnc.250

    Romanian dictionary order, case-insensitive

    SQL_Romanian_Cp1250_CI_AS_KI_WI

    91

    shldic.250

    Croatian dictionary order, case-sensitive

    SQL_Croatian_Cp1250_CS_AS_KI_WI

    92

    shlnc.250

    Croatian dictionary order, case-insensitive

    SQL_Croatian_Cp1250_CI_AS_KI_WI

    93

    skydic.250

    Slovak dictionary order, case-sensitive

    SQL_Slovak_Cp1250_CS_AS_KI_WI

    94

    skync.250

    Slovak dictionary order, case-insensitive

    SQL_Slovak_Cp1250_CI_AS_KI_WI

    95

    slvdic.250

    Slovenian dictionary order, case-sensitive

    SQL_Slovenian_Cp1250_CS_AS_KI_WI

    96

    slvnc.250

    Slovenian dictionary order, case-insensitive

    SQL_Slovenian_Cp1250_CI_AS_KI_WI

    104

    binary.250

    Binary

    SQL_Latin1_General_1251_BIN

    105

    diction.251

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1251_CS_AS_KI_WI

    106

    nocase.251

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1251_CI_AS_KI_WI

    107

    ukrdic.251

    Ukrainian dictionary order, case-sensitive

    SQL_Ukrainian_Cp1251_CS_AS_KI_WI

    108

    ukrnc.251

    Ukrainian dictionary order, case-insensitive

    SQL_Ukrainian_Cp1251_CI_AS_KI_WI

    112

    binary.253

    Binary

    SQL_Latin1_General_1253_BIN

    113

    diction.253

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1253_CS_AS_KI_WI

    114

    nocase.253

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1253_CI_AS_KI_WI

    121

    graltdct.253

    Dictionary order, case-sensitive, accent-sensitive

    SQL_Latin1_General_Cp1253_CI_AS_KI_WI

    124

    grnoacce.253

    Dictionary order, case-insensitive, accent-insensitive

    SQL_Latin1_General_Cp1253_CI_AI_KI_WI

    128

    binary.254

    Binary

    SQL_Latin1_General_1254_BIN

    129

    diction.254

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1254_CS_AS_KI_WI

    130

    nocase.254

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1254_CI_AS_KI_WI

    136

    binary.255

    Binary

    SQL_Latin1_General_1255_BIN

    137

    diction.255

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1255_CS_AS_KI_WI

    138

    nocase.255

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1255_CI_AS_KI_WI

    144

    binary.256

    Binary

    SQL_Latin1_General_1256_BIN

    145

    diction.256

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1256_CS_AS_KI_WI

    146

    nocase.256

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1256_CI_AS_KI_WI

    152

    binary.257

    Binary

    SQL_Latin1_General_1257_BIN

    153

    diction.257

    Dictionary order, case-sensitive

    SQL_Latin1_General_Cp1257_CS_AS_KI_WI

    154

    nocase.257

    Dictionary order, case-insensitive

    SQL_Latin1_General_Cp1257_CI_AS_KI_WI

    155

    etidic.257

    Estonian dictionary order, case-sensitive

    SQL_Estonian_Cp1257_CS_AS_KI_WI

    156

    etinc.257

    Estonian dictionary order, case-insensitive

    SQL_Estonian_Cp1257_CI_AS_KI_WI

    157

    lvidic.257

    Latvian dictionary order, case-sensitive

    SQL_Latvian_Cp1257_CS_AS_KI_WI

    158

    lvinc.257

    Latvian dictionary order, case-insensitive

    SQL_Latvian_Cp1257_CI_AS_KI_WI

    159

    lthdic.257

    Lithuanian dictionary order, case-sensitive

    SQL_Lithuanian_Cp1257_CS_AS_KI_WI

    160

    lthnc.257

    Lithuanian dictionary order, case-insensitive

    SQL_Lithuanian_Cp1257_CI_AS_KI_WI

    183

    danonocp.iso

    Danish/Norwegian dictionary order, case-insensitive, uppercase preference

    SQL_Danish_Pref_Cp1_CI_AS_KI_WI

    184

    islanocp.iso

    Swedish/Finnish[Phone] dictionary order, case-insensitive, uppercase preference

    SQL_SwedishPhone_Pref_Cp1_CI_AS_KI_WI

    185

    svf2nocp.iso.iso

    Swedish/Finnish[Std] dictionary order, case-insensitive, uppercase preference

    SQL_SwedishStd_Pref_Cp1_CI_AS_KI_WI

    186

    islanocp.iso

    Icelandic dictionary order, case-insensitive, uppercase preference

    SQL_Icelandic_Pref_Cp1_CI_AS_KI_WI

0 comments:

Post a Comment

Rss: Top
 

Copyright © 2010 SQL Cached, All Rights Reserved. Design by DZignine