Thursday, January 5, 2017

SQL Server -Binary Check sum

Binary check sum function usually used to detect changes in a row. If any row has any value changed, this function can be used to figure out if the values are changed in the rows. However, if the row is changed from A to B and once again changed back to A, the BINARY_CHECKSUM cannot be used to detect the changes. Let us see quick example of the of same.
create table t1(id int,name varchar(25),country varchar(10))
insert into t1 values(1,'williams','newyork')
insert into t1 values(2,'bond','america')
insert into t1 values(3,'clark','england')
go
create table t2(id int,name varchar(25),country varchar(10))
insert into t2 values(1,'williams','newyork')
insert into t2 values(2,'bond','america')
insert into t2 values(3,'clark','Usa')


select A.id,A.name,A.country,B.id,b.name,b.country from
((select id,name,country from t1) A
inner join (select id,name,country from t2) B
on A.country = B.country)
go

select t1.t1id,t1.t1name,t1.t1country,t1.t1status,t2.t2id,t2.t2name,t2.t2country,t2.t2status from
((
select t1.id as t1id,t1.name as t1name,t1.country as t1country,BINARY_CHECKSUM(t1.id,t1.name,t1.country) as t1status from t1) t1
inner join
(select t2.id as t2id,t2.name as t2name,t2.country as t2country,
binary_checksum(t2.id,t2.name,t2.country) as t2status from t2)  t2
on t1.t1id=t2.t2id
and t1.t1status<>t2.t2status)







No comments:

Search This Blog

DAX - Grouping on multiple columns and the count

Please go thorugh the below URL for entire soultion. http://community.powerbi.com/t5/Desktop/DAX-Grouping-on-multiple-columns-and-the-cou...