4

For decimal numbers of the form [x.00, x.99], where x is any arbitrary integer (you can probably guess part of the context here), floating-point datatypes are generally considered to be fairly inaccurate and sometimes dangerous. Instead, in languages that have decimal datatypes, decimals are often preferred. SQL is one such language.

That being said, here's an interesting little experiment in SQL Server:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
    [SomeFloat] [float] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO Table_1 VALUES (1.1)
SELECT SomeFloat FROM Table_1
SELECT SomeFloat + SomeFloat FROM Table_1
UPDATE Table_1 SET SomeFloat = SomeFloat + SomeFloat
SELECT SomeFloat FROM Table_1

This returns the following:

1.1
2.2
2.2

That shouldn't be, right? x.1 is not supposed to be able to be stored as a floating-point number - not in SQL, not in a good many languages. Instead, shouldn't it be rounded off, due to limitiations when working with base-2?

But it's not. Neither is anything else of the form [x.00, x.99]. They're all represented accuratly, despite using a float datatype.

I can't change some stuff from float to decimal without first being able to justify that there's even a problem in the first place. I'm using SQL Server 2012. Does it automatically pick up on issues like this and convert these numbers into varchars internally or something else like that? How reliable is this? Thanks.

Panzercrisis
  • 4,590
  • 6
  • 46
  • 85
  • 2
    Those numbers *are* being rounded. You just can't tell because it's accurate to nearly 15 decimal places. – Gabe Sep 08 '14 at 15:13
  • As MSDN states, `Values of float are truncated when they are converted to any integer type.` Showing that `float`s are imprecise is a bit more complicated than doing `1.1 + 1.1`. – Luaan Sep 08 '14 at 15:14
  • take a look at this post for reasons you may not want to store in float format: http://decipherinfosys.wordpress.com/2009/04/29/floating-point-arithmetic/ – Steve Ford Sep 08 '14 at 15:16
  • 1
    You are confusing the internal representation of a number with the display format. Just because something looks like `1.1` doesn't mean it really isn't `1.099999999999'. – Gordon Linoff Sep 08 '14 at 15:19

1 Answers1

3

When converting a float to varchar, SQL Server displays a maximum of 6 digits by default. A float can store around 15 digits. That's more than enough to store 1.1 up to six digits behind the dot.

It's easy to show imprecision with the real type, which is a float with less precision. When you use the 2 style of convert to show 16 digits:

select  convert(varchar(max), cast(1.1 as real), 2);
-->
1.100000023841858e+000

The more precise float has enough zeroes to display:

1.100000000000000e+000

Now print seems to use the 0 convert style:

select  convert(varchar(max), cast(1.3 as real), 0)
,       convert(varchar(max), cast(1.3 as real), 1)
,       convert(varchar(max), cast(1.3 as real), 2);
-->
1.3    1.3000000e+000    1.299999952316284e+000

Perhaps this is something like "round to six digits and drop trailing zeroes"? I'm not sure, you'd have to experiment.

I have not found a way to make SSMS display more than 16 digits. But a sufficiently precise client can show that no float can store 1.1 without loss of precision.

Andomar
  • 232,371
  • 49
  • 380
  • 404