EmptyWaterHoles EmptyWaterHoles - 1 month ago 6
SQL Question

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

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?

Answer

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