Numeric Type Priority in T-SQL

Question

I am completely up the tree regarding the way T-SQL handles the priority type with the various numeric types that are used in CASE . Could you explain this with the following test:

 -- Consider the query: SELECT CASE WHEN 1=1 THEN CAST(1.555 AS numeric(16,3)) ELSE CEILING((1+1) * CAST(1 AS Numeric(16,2)) * CAST(1 AS int)) END AS Test -- This returns 2 (scale = 0) -- Now, remove the CEILING function: SELECT CASE WHEN 1=1 THEN CAST(1.555 AS numeric(16,3)) ELSE (1+1) * CAST(1 AS Numeric(16,2)) * CAST(1 AS int) END AS Test -- and it gives 1.56 (scale = 2) -- Now replace (1+1) with 2: SELECT CASE WHEN 1=1 THEN CAST(1.555 AS numeric(16,3)) ELSE (2) * CAST(1 AS Numeric(16,2)) * CAST(1 AS int) END AS Test -- and it yields 1.555 (scale = 3) 

It seems to me that this is wrong, because in all three queries, numeric(16,3) in the 1=1 branches should take precedence over the less accurate result of the ELSE branch.

Answer 1

The problem is that the second case branch has a different data type in each case.

 SELECT CAST(1.555 AS NUMERIC(16, 3)) AS A, CEILING(( 1 + 1 ) * CAST(1 AS NUMERIC(16, 2)) * CAST(1 AS INT)) AS B,--NUMERIC(38,0) CAST(1.555 AS NUMERIC(16, 3)) AS C, ( 1 + 1 ) * CAST(1 AS NUMERIC(16, 2)) * CAST(1 AS INT) AS D,--NUMERIC(38,2) CAST(1.555 AS NUMERIC(16, 3)) AS E, ( 2 ) * CAST(1 AS NUMERIC(16, 2)) * CAST(1 AS INT) AS F --NUMERIC(29,2) INTO T 

Maximum scale for numeric value 38

The first has an else branch of NUMERIC(38,0) , so this is also a return type. If the else branch evaluates to 99999999999999999999999999999999999999 , then nothing will work.

The second has an else branch of NUMERIC(38,2) , so this is the last data type for similar reasons. To save three digits for accuracy, it should be NUMERIC(38,3) , but then 999999999999999999999999999999999999.99 not suitable.

The third has an else branch NUMERIC(29,2) . This is not a maximum scale of 38, so it is possible to expand and maintain the scale a little. The final return type is NUMERIC(30,3) .

This, of course, just translates a little the question of why other branches evaluate everything differently.

In the normal course of events, the following expressions evaluate to int (check the definition of the created table)

 SELECT ( 1 + 1 ) AS A, ( 2 ) AS B INTO T2 

Thus, there is no obvious reason for the different behavior between the two. But I suspect that literal 2 treated as NUMERIC(1,0) by checking the actual value.

While the slightly more complex expression 1 + 1 treated as any unknown integer and as NUMERIC(10,0) .

Then you enter the rules described in detail on this page to preserve accuracy and scaling when multiplying and dividing a number.