varchar vs. nvarchar  
Author Message
voodoochile





PostPosted: Sat Aug 02 09:04:44 CDT 2003 Top

SQL Server Developer >> 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

SQL Server206  
 
 
Vlad





PostPosted: Sat Aug 02 09:04:44 CDT 2003 Top

SQL Server Developer >> varchar vs. nvarchar use N to distriguish a "national" string literal:

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

HTH,
</wqw>



> 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





PostPosted: Sat Aug 02 11:28:50 CDT 2003 Top

SQL Server Developer >> 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




> use N to distriguish a "national" string literal:
>
> select [name] from VARCHAR..[table] where [name] = N'usaer'
>
> HTH,
> </wqw>
>


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


 
 
Steve





PostPosted: Sat Aug 02 11:58:25 CDT 2003 Top

SQL Server Developer >> 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



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