Testquery to compare 2 tables

The most important thing when developing in a datawarehouse environment is testing. If I want to compare 2 tables, ‘old’ versus ‘new’. I often use the code below.

/*Testquery difference between 2 tables*/
--select table old union table new with extra fields
;With ca as (
    select *,typ='old',chk=-1
    from old
    union all
   select *,typ='neww',chk=1
   from neww
)--end ca
,cb as (--make pivottable and compare the rows
    select *, dif=isnull(old,0)+isnull(neww,0)
    from (
        sum(chk) for typ in ([old],[neww])
    ) b
)

-query on the dif field for differences
select * from cb
where dif<>0

If you are working in an sql environment, using an index is important. When missing I use often a clustered columnstore index. No thinking, just use it, very easy.

create clustered columnstore index n001 on old;
create clustered columnstore index n001 on neww;