CODEWITHSUNDEEP
sql-serverprecisiondecimal-precision
Brondahl
Brondahl

Reputation: 8547

SQL calculation output precision

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?

Upvotes: 1

Views: 2475

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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

Upvotes: 1

Related Questions