0
DECLARE @f as float

SELECT @f = 0.2

SELECT @f

The above code returns

----------------------
0.2

(1 row(s) affected)

No surprise there. Except that floating-point cannot represent 0.2 exactly.

So what is SQLServer doing here? Is it using floating-point with a base of 10? I was of the understanding that it always dealt with floating-point in base 2 (as floating-point with base 10 would basically be DECIMAL?).

Is it doing some rounding?

El Ronnoco
  • 11,753
  • 5
  • 38
  • 65
  • 1
    A minor point, but `0.2` is a decimal constant. `0.2E0` is a float constant. It doesn't affect the question thoguh – gbn Jun 18 '13 at 09:40

1 Answers1

1

float is accurate to 15 significant figures so 0.2 will usually come out as 0.2

The techniques mentioned in your link mitigate (but may not eliminate) loss of precision for values like this with few digits

But with larger numbers, precision is lost

DECLARE @f as float

SELECT @f = 0.1234567890123456E0
SELECT @f

SELECT @f = 1.000000000000023E0
SELECT @f
gbn
  • 422,506
  • 82
  • 585
  • 676
  • So what will it actually be storing to represent this? Is it using base 2? – El Ronnoco Jun 18 '13 at 10:07
  • So it will store `10 (b2)` to represent `2 (b10)` and also `1 (b2)` to represent `1 (b10)` to shift the *decimal* point 1 place? – El Ronnoco Jun 18 '13 at 10:16
  • 1
    @ElRonnoco: Floating point uses a mantissa and exponent. So `0.2(b10)` will be a mantissa of `2(b10)` and exponent of `-1(b10)`. Or `2 times (10 to power of -1)`. But in binary equivalent, whatever that is. So `0.2(b10)` will be stored as `x(b2) times (2 to power of y(b2))` – gbn Jun 19 '13 at 11:27