Professional Web Applications Themes

varchar vs. nvarchar - Microsoft SQL / MS SQL Server

I have two servers running SQL 2000 (version 760). The collation property for both is COLLATE SQL_Latin1_General_CP1_CI_AS. Each server has a [database] with a [table], and the first has the [name] field as varchar, while the second has the [name] field as nvarchar. Both tables have a record where [table].[name] = 'usaer', and another record where [table].[name] = 'usær'. When I run these queries agains the database with the VARCHAR field, I get a single record returned: select [name] from VARCHAR..[table] where [name] = 'usaer' --returns 'usaer' select [name] from VARCHAR..[table] where [name] = 'usær' --returns 'usær' When I run ...

  1. #1

    Default varchar vs. nvarchar

    I have two servers running SQL 2000 (version 760). The collation property
    for both is COLLATE SQL_Latin1_General_CP1_CI_AS. Each server has a
    [database] with a [table], and the first has the [name] field as varchar,
    while the second has the [name] field as nvarchar. Both tables have a record
    where [table].[name] = 'usaer', and another record where [table].[name] =
    'usær'.

    When I run these queries agains the database with the VARCHAR field, I get a
    single record returned:

    select [name] from VARCHAR..[table] where [name] = 'usaer'

    --returns 'usaer'

    select [name] from VARCHAR..[table] where [name] = 'usær'

    --returns 'usær'




    When I run these queries agains the database with the NVARCHAR field, I get
    two records returned:

    select [name] from NVARCHAR..[table] where [name] = 'usaer'

    --returns 'usaer' and 'usær'

    select [name] from NVARCHAR..[table] where [name] = 'usær'

    --returns 'usaer' and 'usær'



    If I run this on the server with the NVARCHAR version, I get the following
    result:

    IF 'usaer' = 'usær'

    SELECT 'y'

    ELSE SELECT 'n'

    --returns 'n'

    So why, in my NVARCHAR version, do I get inconsistent results as to whether
    'usaer' = 'usær' ?

    Thanks,

    -Jean


    Jean Guest

  2. #2

    Default Re: varchar vs. nvarchar

    use N to distriguish a "national" string literal:

    select [name] from VARCHAR..[table] where [name] = N'usaer'

    HTH,
    </wqw>

    "Jean Rynearson" <com> wrote in message
    news:phx.gbl... 
    record 

    get 
    whether 


    Vlad Guest

  3. #3

    Default Re: varchar vs. nvarchar

    Thanks, but I still get the same result. However, if I run this I get the
    expected result:

    select [name] from VARCHAR..[table] where cast([name] as varchar(64)) =
    N'usaer'

    But converting my queries to use varchar seems to defeat the purpose of
    storing international data.

    -Jean


    "Vlad Vissoultchev" <myrealbox.com> wrote in message
    news:#phx.gbl... [/ref]
    property [/ref]
    varchar, 
    > record [/ref]
    = [/ref]
    get 
    > get [/ref]
    following 
    > whether 
    >
    >[/ref]


    Jean Guest

  4. #4

    Default Re: varchar vs. nvarchar

    Jean,

    The Books Online article "Collation Settings in Setup" says

    "The SQL Collations option is used for compatibility with
    earlier versions of Microsoft SQL Server." It may be that
    some people need to preserve this inconsistent behavior
    when upgrading.

    If you use a Windows collation instead of a SQL collation, I
    think you will get consistent behavior. I tried comparing the
    strings usaer and usær with Latin1_General_CI_AS and found
    that they are considered equal both as varchar or nvarchar
    strings.

    If you need a collation where 'usaer' <> 'usær', you can use
    a Danish_Norwegian collation, where the strings will be
    considered different, whether stored as ASCII or Unicode
    strings.

    By the way, I don't think you are getting inconsistent behavior.
    You are always seeing usaer and usær as different when
    when doing a varchar comparison and you are always seeing
    them as the same when doing an nvarchar comparison.
    Your final example is not influenced by the existence of an
    nvarchar column somewhere on the same server - it just does
    a varchar comparison, so the strings are unequal.

    -- Steve Kass
    -- Drew University
    -- Ref: DD2D1003-2B93-4F56-A25A-15AD30C1A4BA

    Jean Rynearson wrote:
     

    Steve Guest

Similar Threads

  1. NVARCHAR(MAX) not returned from system views
    By parmstrong in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 31st, 07:49 PM
  2. Retriving nvarchar value from stProc
    By Neven in forum ASP Database
    Replies: 2
    Last Post: November 4th, 11:55 AM
  3. Adding numbers from a nvarchar field
    By JayG in forum ASP Database
    Replies: 4
    Last Post: January 22nd, 02:39 PM
  4. Which ADO constant for SQL server NVarchar type?
    By Iain in forum ASP Database
    Replies: 1
    Last Post: November 18th, 12:08 AM
  5. nvarchar Question
    By Don Grover in forum ASP
    Replies: 1
    Last Post: October 11th, 01:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139