I have a particular table with a column data type of float(53), non null. According to the Microsoft Documentation, the smallest value above 0 that can be stored in this data type is is 2.23E-308. However, I am able to store a value much smaller, as small as 4.94065645841247E-324. See the following image of proof that SQL server can store and retrieve a value outside the range specified in the documentation:
When I run a query such as the following:
SQL Server says:
Warning: the floating point value '4.7E-309' is too small. It will be interpreted as 0.
The value inserted in the screenshot was inserted via the C++ ADO interface using record binding.
I have a few questions:
Why does SQL server handle values outside the range specified in the documentation? Similarly, why does SQL Server issue a warning when an "out of range" value is in the query, when clearly the database can store values out of that range?
How are floating points stored on SQL database tables, and are there any SQL server settings that would strictly enforce the range on one SQL server as opposed to another?
I'm trying to troubleshoot an issue where an ADO record binding was (possibly) rejected due to the value being out of range, however all my test to reproduce the issue (on a different SQL server instance) show that SQL accepts values much beyond the limits specified in the documentation. Any information on how floating points are stored and handled in SQL (especially with relation to something like .NET or C++) would be much appreciated.
I have tested this with both SQL server 2005 and 2008 with the same results.