CODEWITHSUNDEEP
sql-server
BoCoKeith
BoCoKeith

Reputation: 946

SELECT DISTINCT Returning Unexpected Values

My SQL Server 2016 database is using collation SQL_Latin1_General_CP1_CI_AS. When I run this query...

select distinct x from (
select '²' as x
union all
select '2'
) y 

select distinct x from (
select N'²' as x
union all
select N'2'
) y

The VARCHAR version sees the two characters as being different; the NVARCHAR version sees them as being the same.

What the heck is going on?

Upvotes: 1

Views: 202

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

The VARCHAR version sees the two characters as being different

Default CoLLATE (COLLATE SQL_Latin1_General_CP1_CI_AS) of database is doing the trick here.

COLLATE SQL_Latin1_General_CP1_CI_AS consider varchar value '²' and '2' as 2 different value.

In case of NVARCHAR COLLATE SQL_Latin1_General_CP1_CI_AS consider '²' and '2' as same value.

It has nothing to do with ACSII or UNICODE

Upvotes: 1

Laughing Vergil
Laughing Vergil

Reputation: 3756

Apparently, they are considered the uppercase and lowercase version of the same character as nvarchar. If you run this case sensitive query:

select Distinct x from (
select N'2' COLLATE SQL_Latin1_General_CP1_CS_AS  as x
UNION ALL
select N'²' COLLATE SQL_Latin1_General_CP1_CS_AS as x
) y

You will get this output:

x
2
²

Upvotes: 1

Related Questions