Search code examples
sql-serverprecisionnumeric

Microsoft SQL Server : numeric constants precision

I'm puzzled by the lack of precision in this result:

select convert(numeric(11, 10), 1 / 3.0)

   0.3333330000

Why only six decimal places when I asked for ten? Here I wrote 3.0 instead of 3 to force floating point division instead of integer division (the value of 1/3 is zero).

But what type is that constant 3.0? It doesn't seem to be any of the native SQL Server floating point types, which all give a different result:

select convert(numeric(11, 10), 1 / convert(real, 3))

   0.3333333433

select convert(numeric(11, 10), 1 / convert(double precision, 3))

   0.3333333333

select convert(numeric(11, 10), 1 / convert(float, 3))

   0.3333333333

Until now I have tried to write 3.0 to get a floating point constant, as would happen in programming languages like C. But that isn't the interpretation in SQL. What's happening?

Solution

  • The answer is that 3.0 does not give a constant of floating point type, but one of numeric type, specifically numeric(2,1). You can see this using:

    select sql_variant_property(3.0, 'basetype') as b,
       sql_variant_property(3.0, 'precision') as p,
       sql_variant_property(3.0, 'scale') as s
b: numeric
p: 2
s: 1

    If you specify this type explictly you get the same odd limited-precision result:

    select convert(numeric(11, 10), 1 / convert(numeric(2, 1), 3))
   0.3333330000

    It's still odd -- how did we end up with six decimal places, when the original numeric constant had only one and the output type wants ten? By the rules of numeric type promotion we can see that the division expression gets type numeric(8, 6):

    select sql_variant_property(1 / convert(numeric(2, 1), 3), 'basetype') as b,
       sql_variant_property(1 / convert(numeric(2, 1), 3), 'precision') as p,
       sql_variant_property(1 / convert(numeric(2, 1), 3), 'scale') as s
b: numeric
p: 8
s: 6

    Why exactly the reciprocal of something with one d.p. should get six d.p., rather than five or seven, is not clear to me, but I guess SQL has to pick something.

    The moral of the story is not to write numeric constants like 1.0 in SQL expecting to get floating point semantics. You will get numeric semantics, but probably with too few decimal places to be useful. Write convert(1, float) or 1e instead if you want floating point, or else use numeric type and check that each step of the calculation has the number of d.p. you want.

    Thanks to colleagues who pointed me to https://bertwagner.com/posts/data-type-precedence-and-implicit-conversions/ which explains some of this.