Compare two tables and data in SQL Server

Recently I worked on the SP performance tuning. My existing SP took 50 Minutes to run. After tuning this SP it is taking approx. 24 minutes. I made some logical and structural changes in my SP. At the end we need to validate the result of both SP (existing and after changes).

For the testing purpose I create two databases and loading the data form PROD. In one data base I am keeping existing SP which took 50 min to run and another database I have new SP.

The main of my SP to manipulate data from the different table and load it into one table. After executing both SP dates are loaded on the target table.

Now we need to validate the result of both tables. Doing the manual data validation is not possible because my table is having approx. 82 M records.

With the help of EXCEPT or joins we can validate the result. Let’s see the example.

Here for the example I have a table EMP_SOURCE and EMP_TARGET we will check both table’s data.

See the records on both tables.

  

In this given example there is some discrepancy. Let’s see

  

Again we need use EXCEPT with EMP_SOURCE

  

Here we got data. It means both tables are not having same number of records and same data.

We can also get this information using Join(Left and Right). If we get null value in the columns it means not same records in the both table. 

Right join

It means some records are not in the Source table.

If in both table having same records then EXCEPT return no records and joins return not null records.

Now I am keeping same records in both tables.

 

 

Using Except

Using joins