views:

27

answers:

1

When I am writing queries, I will do so in steps. Sometimes, in the process I will realize that I've made a "mistake" such as ending up with extra or losing records. So, I will typically compare the two queries like so:

(Select blah blah blah ) Mine 
Inner join 
((Select blah blah blah ) Orig
Where Mine.PK <> Orig.PK

or if I'm looking for missing or extra records I will use a left join instead and look for nulls.

Is there a better way to quickly figure out why two queries are returning different numbers of records?

+2  A: 

You need a full join to start with. Inner join will show you only the records that are in Mine but not in Orig. A full join will show you also those that are in Orig and missing in Mine.

A quick and dirty way to check for differences is to compare the result of CHECKSUM_AGG(CHECKSUM(*))

Remus Rusanu