SQL calculation output precision

Question

Could someone explain the behaviour of this query:

SELECT
    0.1 + 0.01,
    '',
    (CAST(0.1 AS numeric(18,1)))+(CAST(0.01 AS numeric (18,2))),
    0.1 + (CAST(0.01 AS numeric (18,2))),
    (CAST(0.1 AS numeric(18,1)))+ 0.01,
    '',
    (CAST(0.1 AS numeric(38,1)))+(CAST(0.01 AS numeric (38,2))),
    0.1 + (CAST(0.01 AS numeric (38,2))),
    (CAST(0.1 AS numeric(38,1)))+ 0.01

I don't understand why the 38 behaves differently from the 18?

I was expecting that SQL server would always automatically return a calculation result at the precision necessary to accurately display the result? That to change that, you'd have to explicitly cast the result?

dba.stackexchange.com/a/41745/95107 and you'll notice, since you are only using 2 precision, using 37 for the scale will return the same results as 18 — S3S
– S3S, Commented Mar 8, 2017 at 17:40

SqlZim · Accepted Answer · 2017-03-08 17:52:12Z

It follows the rules found here: Precision, Scale, and Length (Transact-SQL) - msdn

When you are using the maximum precision, it is not able to adjust the scale without risking losing precision.

Chart for Data Type Conversion (Database Engine) - msdn

If you introduced a float into your calculations, you would get a float return.

By default, values with a decimal will be implicitly cast to decimal/numeric datatypes. Those implicit conversions are marked as 'und' in an example modified from your question:

select
      undefined=0.1 + 0.01
    ,[18,1+18,2] = (cast(0.1 as numeric(18,1)))+(cast(0.01 as numeric (18,2)))
    ,[und+18,2+18,1] = 0.1 + (cast(0.01 as numeric (18,2)))
    ,[18,2+und]=(cast(0.1 as numeric(18,1)))+ 0.01
    ,[38,1+38,2]=(cast(0.1 as numeric(38,1)))+(cast(0.01 as numeric (38,2)))
    ,[und+38,2] = 0.1 + (cast(0.01 as numeric (38,2)))
    ,[38,1+und]=(cast(0.1 as numeric(38,1)))+ 0.01
    ,[38,1+float]=(cast(0.1 as numeric(38,1)))+ convert(float,0.01)

rextester demo: http://rextester.com/ULVRGS77309

returns:

+-----------+-----------+---------------+----------+-----------+----------+----------+------------+
| undefined | 18,1+18,2 | und+18,2+18,1 | 18,2+und | 38,1+38,2 | und+38,2 | 38,1+und | 38,1+float |
+-----------+-----------+---------------+----------+-----------+----------+----------+------------+
| 0,11      | 0,11      | 0,11          | 0,11     | 0,1       | 0,11     | 0,1      | 0,11       |
+-----------+-----------+---------------+----------+-----------+----------+----------+------------+

Collectives™ on Stack Overflow

SQL calculation output precision

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related