Binaryworld - How to use the CHECKSUM function to find orphan rows & duplicates? ... [ SQL Server -> Performance ]

create schema authorization sa
create table #t1 (
i1 int , c1 char( 1 ) , vc1 varchar( 10 )
)
create table #t2 (
i2 int , c2 char( 1 ) , vc2 varchar( 10 )
)
go

insert into #t1 values( 1 , '1' , '1111' );
insert into #t1 values( 11 , 'a' , 'aaaa' );
insert into #t2 values( 1 , '1' , '1111' );
insert into #t2 values( 2 , '2' , '2222' );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where not exists ( select * from (
            select * , checksum( * ) as chksum2 from #t2
          ) as t2
          where chksum1 = chksum2
    );

-- Orphans in #t2 only
select i2 , c2 , vc2 from
(
select * , checksum( * ) as chksum2 from #t2
) as t2
where not exists ( select * from (
            select * , checksum( * ) as chksum1 from #t1
          ) as t1
          where chksum1 = chksum2
    )

-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from
(
select * , checksum( * ) as chksum1 from #t1
) as t1
where exists ( select * from (
            select * , checksum( * ) as chksum2 from #t2
          ) as t2
          where chksum1 = chksum2
    );
go

-- You can simplify this further if you define a computed column for
-- the checksum like:
alter table #t1 Add chksum as ( checksum( i1 , c1 , vc1 ) );
alter table #t2 Add chksum as ( checksum( i2 , c2 , vc2 ) );
go
-- Orphans in #t1 only
select i1 , c1 , vc1 from #t1 as t1
where not exists ( select * from #t2 as t2
          where t1.chksum = t2.chksum
    );

-- Orphans in #t2 only
select i2 , c2 , vc2 from #t2 as t2
where not exists ( select * from #t1 as t1
          where t1.chksum = t2.chksum
    )

-- Matching rows in #t1 & #t2
select i1 , c1 , vc1 from #t1 as t1
where exists ( select * from #t2 as t2
          where t1.chksum = t2.chksum
    );
go
drop table #t2;
drop table #t1;
go