Why does MS-SQL-Server (all versions) cast 1.0/12.0 as numeric(8,6)?

Question

I'm trying to explain a 101.10 difference in value.

303'300'000/12 is about 25'275'000.
However, according to MS-SQL, it's 25'274'898.90.

Consider this (dumbed down) SQL-statement:

SELECT 
     303300000.00/12.0 AS a 
    ,1.0/12.0*303300000.00 AS b
    ,1.0/12.0 AS omg 
    ,1.0/CAST(12.0 AS float) AS expected 
    ,0.083333*303300000.0 AS r1 
    ,0.083333333333333300 * 303300000.0 AS r2

I thought since I wrote 1.0/12.0 it would cast as float
(which is itselfs dumb, but that's another story),
but apparently, it's decimal (8,6)

CREATE VIEW dbo._Test
AS
SELECT 1.0/12.0 as xxx



SELECT 
     COLUMN_NAME
    ,DATA_TYPE
    ,NUMERIC_PRECISION
    ,NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '_Test' 


DROP VIEW dbo._Test

Is there any reason to this madness ?
How does it determine the precision ?
Is there any notation to force float, without the need for a cast statement ?

If I run the same query on PostgreSQL, pg does it right...

This is [documented under "Precision, Scale and Length"](https://learn.microsoft.com/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017). The rules are not easy to digest, and they attempt to preserve as many digits before the decimal point before they go to preserving as many after the decimal point. `FLOAT` constants can be constructed by using `E` notation, which can be as simple as `1e`. Of course, binary floating-point comes with its own precision issues... — Jeroen Mostert, Aug 10 '18 at 13:55
Incidentally, a more convenient way of determining the type of your expression than creating a table and querying the metadata is using `SQL_VARIANT_PROPERTY`: `SELECT SQL_VARIANT_PROPERTY(1.0/12.0, 'BaseType'), SQL_VARIANT_PROPERTY(1.0/12.0, 'Precision'), SQL_VARIANT_PROPERTY(1.0/12.0, 'Scale')`. — Jeroen Mostert, Aug 10 '18 at 14:03
@Jeroen Mostert: I know what the machine epsilon is, so no surprises there. Interesting, so correct is 1.0e/12.0e — Stefan Steiger, Aug 10 '18 at 14:04

score 6 · Accepted Answer · answered Aug 10 '18 at 13:58

This is explained in the documentation: Precision, scale, and Length (Transact-SQL)

Specifically it states:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.
Operation     Result precision                        Result scale *
e1 + e2       max(s1, s2) + max(p1-s1, p2-s2) + 1     max(s1, s2)
e1 - e2       max(s1, s2) + max(p1-s1, p2-s2) + 1     max(s1, s2)
e1 * e2       p1 + p2 + 1                             s1 + s2
e1 / e2       p1 - s1 + s2 + max(6, s1 + p2 + 1)      max(6, s1 + p2 + 1)

The important part here is the last one. In your case, you have a decimal(2,1) and a decimal(3,1). For precision, this results in:

 2 - 1 + 1 + max(6,1 + 3 + 1) = 2 + max(6,5) = 2 + 6 = 8

For the scale, we get:

max(6,1+3+1) = max(6,5) = 6

Take the resulting values and as a result you get a decimal(8,6).

score 2 · Answer 2 · answered Aug 10 '18 at 13:54

Setting the precision and scale for multiplication and division of decimal/numeric is a mystical art. SQL Server does explain the rationale in the documentation:

In multiplication and division operations we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:

The resulting scale is reduced to min(scale, 38 – (precision-scale)) if the integral part is less than 32, because it cannot be greater than 38 – (precision-scale). Result might be rounded in this case.

The scale will not be changed if it is less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it cannot fit into decimal(38, scale)

The scale will be set to 6 if it is greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or overflow error will be thrown if integral part cannot fit into 32 digits.

Because I have never had a need to implement this functionality, I must honestly say that I'm not sure I have ever really understood the logic (I'm sure it makes a lot of sense). My take-away from the explanation is: If I care about the precision and scale of the result, then I'll cast after the arithmetic operation.

Somewhere in that explanation I see "6", so that explains the scale part. I'm sure the precision follows from equally direct logic.

score 0 · Answer 3 · answered Aug 10 '18 at 14:05

Consider the fact that literals like 1.0 and 12.6 are of decimal (numeric) type. 1.0 is precision 2 and scale 1, 12.0 is precision 3 and scale 2.

The result of the expression 1.0/12.0 is of type decimal (numeric) because the highest precendence of the types is decimal (both types are decimal).

As for the precision and scale of the expression, the formulas are:

expr        precision                           scale
e1 / e2     p1 - s1 + s2 + max(6, s1 + p2 + 1)  max(6, s1 + p2 + 1)

So,

precision=2-1+1+max(6,1+3+1)=2-1+1+6=8

and

scale=max(6,1+3+1)=6

Why does MS-SQL-Server (all versions) cast 1.0/12.0 as numeric(8,6)?

3 Answers3