CODEWITHSUNDEEP
sql-serverfloating-pointprecision
CS Pei
CS Pei

Reputation: 11047

SQL Server has float number less than 2.23E-308 (minimum of double precision number)

We are using SQL Server 2014 to store results from a C library. Recent we found that there are some values like 1.39202924295074E-309 stored in a column defined as a float.

This is weird because based on MSDN, the float range is 

 -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

by default which is consistent with any literature I found.

So the question is why we got value smaller than the minimum allowed?

The table is designed something like

CREATE TABLE [dbo].[CLibDataResult](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Result] [float] NOT NULL
)

Upvotes: 2

Views: 1274

Answers (3)

Cato
Cato

Reputation: 3701

if you start off with this

update [CLibDataResult] set result = power(cast(10.000 as float),-300);

or 

 INSERT [CLibDataResult] VALUES(power(cast(10.000 as float),-300));

and then repeatedly run 

update [CLibDataResult] set result = result / 10;
select * from [CLibDataResult];

you can watch the value get smaller, then around e-308 its precision seems to fall to pieces as there are less digits somehow available, finally it just vanishes to zero

Upvotes: 0

CS Pei
CS Pei

Reputation: 11047

After some digging on the internet, I think I can answer my question. One of the references is this page and this page.

There are two concepts here, one is the minimum positive normal number and the minimum positive subnormal number. The MSDN page lists the ranges based on the minimum positive normal number. But based on IEEE 754, the minimum positive subnormal number is the smallest positive number representable in the double-extended format.

Here is a cheat sheet for these numbers for double precision,

--------------------------------------------------------------
| max normal number             |   1.7976931348623157e+308  |
--------------------------------------------------------------
| min positive normal number    | 2.2250738585072014e-308    |
--------------------------------------------------------------                                           
| max subnormal number          |   2.2250738585072009e-308  |
--------------------------------------------------------------                                           
| min positive subnormal number |   4.9406564584124654e-324  |
--------------------------------------------------------------

In conclusion, the minimum representable is 4.9406564584124654e-324 and 1.39E-309 is larger than that. So SQL server conforms to IEEE standard for double precision.

Upvotes: 5

Ash8087
Ash8087

Reputation: 701

Strange as it may seem, the value you give is within the range. If you use .NET, you can test it with this code:

var d = double.Parse("1.39202924295074E-309");
var min = double.Parse("-1.79E+308");            
Console.WriteLine(d > min);  // returns true

I think the MSDN text is confusing.

Upvotes: 0

Related Questions