Identify rows that match on a key but differ in other columns Supposed I have table temp A and table temp B:

IF OBJECT_ID('tempdb..#A') IS NOT NULL
BEGIN DROP TABLE #A END

IF OBJECT_ID('tempdb..#B') IS NOT NULL
BEGIN DROP TABLE #B END

CREATE TABLE #A 
(
   ID Int,
   Descr VARCHAR(100),
   Qty Int
)

CREATE TABLE B
(
   ID INT,
   Descr VARCHAR(100),
   Qty INT
)

INSERT INTO #A VALUES (1, 'Data 1' , 10)
INSERT INTO #A VALUES (2, 'Data 2', 5)

INSERT INTO #B VALUES (1, 'Data x' , 8)
INSERT INTO #B VALUES (2, 'Data x' , 1)


The ID is a key in this case. The problem is, how we can fetch the column value differences and showing the column differ on our select query?

I have tried the following:

using except

SELECT * FROM A
EXCEPT
SELECT * FROM B


using join and case.. when

select Case When #A.Descr = Before.Descr THen Null Else #A.Descr End,
       Case When #A.Qty = Before.Qty THen Null Else #A.Qty End
from #A
Left Join #B Before on #A.ID = Before.ID


This query shows me if different values exist then it will show the differences column, else if no difference value found on certain column, then will show null value.

Can you recommend a better solution?
I think that this is what you're looking for...

WITH AllData AS (
  -- Get All Records that exist in both tables
  SELECT ID, Descr, Qty FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
  UNION ALL
  SELECT ID, Descr, Qty FROM B WHERE EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
)
SELECT ID, Descr, Qty
FROM AllData
GROUP BY ID, Descr, Qty
HAVING COUNT(*) = 1
ORDER BY ID


It first selects all of the rows where the ID exists in both tables (since if an ID exists in only one table, it can't have different values in the other table.)  Then it groups by the columns that you want to compare.  If the count is > 1, then they matched.  If the count = 1 then there was a difference.  Here is the SQL Fiddle that I used to verify this.

Edit:  For large data sets, the performance will likely be poor.  But indexing your tables should help with that.