Search code examples
sql-servert-sqlprecision

SQL Server truncates decimal points of a newly created field in a view

I have a view in SQL server, something like this:

select 6.71/3.41 as NewNumber

The result is 1.967741 (note 6 decimal points) -> decimal (38,6)

I try the same thing in a calculator but the result is 1.967741935483871xxxx

I want to force SQL Server to return more accurate result something like decimal(38,16) I have tried the obvious things like casting, but SQL Server doesn't improve the output I just get some trailing zeros at the end like 1.9677410000

Is there a way to force SQL Server to not truncate the result or give more accurate one?

Solution

  • If you want something like decimal(38,16) then you need to cast the inputs not the output after truncation has already occurred!

    SELECT CAST(6.71 AS DECIMAL(38,18))/3.41 AS NewNumber

    Returns

    1.9677419354838709

    Check the datatype

    SELECT 
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'BaseType'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Precision'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Scale')

    Returns

    numeric 38  16

    ##Edit

    This is just to add an additional link as follow up to the comments. The rules for datatypes resulting from decimal to decimal operations are described in BOL.

    +-----------+------------------------------------+---------------------+
| Operation |          Result precision          |   Result scale *    |
+-----------+------------------------------------+---------------------+
| e1 / e2   | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
+-----------+------------------------------------+---------------------+

    That link includes the following phrase

    *The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

    but leaves it unspecified exactly how such truncation is performed. This is documented here (wayback machine link).