Storing IEEE 754 Floats in SQL Server 2005 (NaN, +/- Infinity)
James C. Papp
11/20/2006 12:18:12 PM
User(s) can reproduce this bug
Umachandar Jayachandran recommend that I post this issue on MS Connect. The problem is a breaking change from SQL Server 2000, which prevents us from switching over to SQL Server 2005. The change eliminates the ability to store NaN and Positive and Negative infinity values for double precision floats.
I have a hard time understanding way that this is not supported, or more accurately, why it is prevented in SQL Server 2005 (even if it requires some form of a special field type or extension). It is incredible limitation that affects all of our scientific applications and the storage of our simulation data.
It should be noted that you can store these values even in MS Access. Oracle has specific support for IEEE values as well. In SQL Server 2005, it seems that a decision was made to change the way it validates data being passed from the client. If it encounters one of these special values, it now throws an exception under .NET with the following text:
`The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@PropertyValue"): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.'
My question is why NaN, +/- infinity are considered "bad" values? They shouldn't be. If someone wants financial accuracy they should be using decimal fields, not floating point. Why have the floating point fields at all if you cannot store the IEEE 754 double values in them? Even just letting the values be stored in the database with limitations on doing TSQL queries directly on them would still be an improvement them rejecting them outright. How are you dealing with C# 3.0 and DLinq support? Don't you have the same issues there?
Is there is any chances of getting a hot fix to restore the behavior of SQL Server 2000? Or is there time to work on a solution with the upcoming Service Pack 2? It really is burden to scientific applications.
More information can be found here:
James C. Papp
This is issue was close without explanation, so I reactivated it. Your help is greatly appreciated!
11/30/2006: Respose to comment follows...
Thanks for the reply, but I still have a few concerns. First, if you still come to the conclusion that this is by design, you should seriously consider enhancing SQL Server so it can handle these special floating type values internally. Oracle has support for these with their BINARY_FLOAT and BINARY_DOUBLE data types and it would be nice to see SQL Server at the same level.
With that said, I can see your position with NaN values, but are you sure you were not too draconian in your decision to excluded positive and negative infinity? With NaN, there is no defined sort order unless one is arbitrary assigned, so I can see potential problems with building indexes and such. However, positive and negative infinity do have a well defined sort order, and work fine in calculations, so I still do not understand why you need to excluded these values along with NaN.
These changes seem to stem from Microsoft Article ID 900335 (http://support.microsoft.com/kb/900335) and related documents, but as far as I can tell; most of the information singles out NaN as the culprit (as well as your comment), not positive and negative infinity. I just want to make sure that the "fix" was not overzealous in excluding values that it does not really need to. We could certainly work around not having NaN, by mapping these values to null, but in the future; especially for scientific applications like ours, it would be nice to see full support for the IEEE 754 spec for all special values and conditions, including positive and negative zero.
As far as your solution using varbinary(8) columns, we have considered this, but the main problem is enabling are analysts' access to the data. They use a variety of tools (many with only ODBC connectivity) as well as Microsoft Office (particularly Excel) and the varbinary data type does not come over well or map correctly as it does when stored as a floating point data type. If you have a way to work around this or have other solutions I'd love to hear it. Thanks.