Why are there differences in data when comparing table to itself? TSQL

Question

I'm comparing two tables that should have the same data. I've got a couple of different queries that are supposed to return rows that don't match. However, the queries return rows that DO match. I'm not sure why. Here is an example:

select 
which = 'TableA',
*
from (
select * from compareTempA
except
select * from compareTempB
) x
union all
select
'TableB',
*
from (
select * from compareTempB
except
select * from compareTempA

) x
order by dateindex, empid, serviceSales, productSales

For some reason I get results like the following:

As you can see these rows appear to match. I've also tried:

select sum(case when A.productSales = B.productSales then 0 else 1 end) from 
compareTempA A
join compareTempB B
on A.dateindex = B.dateindex
and A.empid = B.empid
and A.storeid = B.storeid

To which I get 3011. I'm baffled because the data is supposed to be the same in both tables. The only thing I can think of is that I formed the two tables by inserting into each of them in the following way:

insert into compareTempA
select * from someView

And I did the same for compareTempB. I tried truncating the tables first and reinserting. Please help!

Answer 1

The only thing that comes to my mind is that you are not using the same data type for the column productSales on both tables.

For example, if your data type on compareTempA is float and on compareTempB is decimal, that's exactly what should happen.

By the way, this happens even if both columns are float, due to the fact that float is not a precise datatype.

I suggest that you change your datatype to a precise one, like decimal.

"Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24)." Source: https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Similar problems:

Same problem here - https://stackoverflow.com/questions/16149966/comparing-2-float-values-in-sqlserver

Best Pratices Red Gate - https://documentation.red-gate.com/codeanalysis/best-practice-rules/bp023

Another question about floats and precise comparisions - https://stackoverflow.com/questions/10737004/should-we-use-float-as-primary-key-in-sql-server