SQL Server Columns are equal but showing not equal. How are they different?

Question

I have the following code

select 
    Name, UniqueId, 
    checksum(Name) as CheckName, 
    checksum(UniqueId) as CheckId
from 
    DataManagementPipeline.dbo.pod_1801_energex_vegetation_zones 
where 
   Name <> UniqueId

The results are the following

Name        UniqueId        CheckName      CheckId
********************************************************
VZ-4820/73  VZ-4820/73     -1880307869     -21513965
VZ-400706   VZ-400706       591267130      536293334

The values are the same (white space and all) yet they are appearing as different and interestingly the checksums are different. Is it an encoding issue as to why they are different? Any ideas?

The datatypes are both varchar. Name is varchar(max) and UniqueId is varchar(100). But both strings are obviously less that this so it should not make a difference. — Darren Wood
– Darren Wood, Commented Oct 14, 2016 at 4:50
If this condition "Name <> UniqueId" satisfies values will not be retrieved as of my knowledge...how can u generate output for this — yuvi
– yuvi, Commented Oct 14, 2016 at 4:52
That is the mystery. They are the same values in notepad++, etc. They look the same, yet they satisfy <> . The checksum shows something is strange but I can not put my finger on it. — Darren Wood
– Darren Wood, Commented Oct 14, 2016 at 4:55
If both datatype are same ,the values retrieved in output will be same Name UniqueId CheckName CheckId VZ-4820/73 VZ-4820/73 1347317154 1347317154 VZ-400706 VZ-400706 1426899811 1426899811 — yuvi
– yuvi, Commented Oct 14, 2016 at 4:58

Venkataraman R · Accepted Answer · 2016-10-14 04:55:06Z

2

CHECKSUM will return different values, if the types are different. See more at MSDN Checksum. I think in your case, Name & UniqueId are of different types. Please see the example code below

CREATE TABLE test(origname varchar(36), uniqueid nvarchar(36))

INSERT INTO test(origname,uniqueid)
values ('venkat',N'venkat')

SELECT CHECKSUM(origname), CHECKSUM(uniqueid) FROM test

-- Returned Values 
178987073   1792344567

answered Oct 14, 2016 at 4:55

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Darren Wood Over a year ago

Name and UniqueId are varchar(max) and varchar(100) respectively.

yuvi Over a year ago

I have tried with ur case as"varchar(max) and varchar(100) ". I can able to get the output where as vales are same

Darren Wood Over a year ago

The fact that they are both varchar and the same size should not make them unequal. So the mystery continues.

Darren Wood Over a year ago

@Raja Yuvi are you saying that they satisfy <> and are thus returned or they do not satisfy <>?

yuvi Over a year ago

@EmptyWaterHoles if " Name <> UniqueId" values should not be retrieved , if " Name = UniqueId" the values retrieved will/must be same.

|

Darren Wood · Accepted Answer · 2016-10-14 05:42:24Z

2

The mystery solved is that the spacing on the end of UniqueId was ASCII character(10) and the not the white space I thought it was. Hence why they were coming up as not equal. So i added this code

ltrim(rtrim(Name)) <> rtrim(ltrim(REPLACE(REPLACE(UniqueId, CHAR(13), ''), CHAR(10), '')))

In notepad++ I thought I had added the ASCII(10)(linefeed) but apparently not. I will accept the answer by @Venkataraman R as he is correct. Thanks for your help.

answered Oct 14, 2016 at 5:42

Darren Wood

1,5665 gold badges27 silver badges54 bronze badges

Collectives™ on Stack Overflow

SQL Server Columns are equal but showing not equal. How are they different?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related