SQL Server join question

Question

This is on Microsoft SQL Server. We have a query where we are trying to join two tables on fields containing numeric data.

One table has the field defined as numeric(18,2) and the other table has the field defined as decimal(24,4). When joining with the native data types, the query hangs and we run out of patience before it will finish (left it running 6 min…). So we tried casting the two fields to be both numeric(18,2) and the query finished in under 10 seconds. So we tried casting the two fields to be both decimal(18,2) and again the query hangs. Does anyone know the difference between the decimal and numeric data types that would make them perform so differently?

Answer 1

DECIMAL and NUMERIC datatypes are the one and the same thing in SQL Server.

Quote from BOL:

Numeric data types that have fixed precision and scale.

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

From that, I'm surprised to hear of a difference. I'd expect the execution plans to be the same between the 2 routes, can you check?

Answer 2

My guess is that it's not a matter of a specific difference between the two data types, but simply the fact that SQL Server needs to implicitly convert them to match for the join operation.

I don't know why there would be a difference from your first query and the second, where you explicitly convert, but I can see why there might be a problem when you convert to a datatype that doesn't match and then SQL Server has to implicitly convert them anyway (as in your third case). Maybe in the first case, SQL Server is implicitly converting both to decimal(24,4) so as not to lose data and that operation takes longer than converting the other way. Have you tried explicitly converting the numeric(18,2) to a decimal(24,4)?

Answer 3

Why are you using two datatypes to begin with? If they contain the same type of data (and joining on them implies they do), they should be the same datatype. Fix this and all your problems go away. Why waste server resources continually casting to match two fields that should be defined the same?

You of course may need to adjust the input variables for any insert or update queries to match waht you chose as the datatype.