Clash Royale CLAN TAG#URR8PPP

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

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)

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...

FLOAT

E

1e

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')

3 Answers
3

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:

decimal(2,1)

decimal(3,1)

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).

decimal(8,6)

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:

decimal

numeric

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.

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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.