| Groups | Blog | Home |
sql server programming : Reliability of saving C# doubles in SQL Server... infinities and NaNs?
I am writing an engineering solution on top of Office and SQL Server.
Dealing with IEEE double-precision floating point is always a pain, even with software tools that support it. It appears SQL Server does not even claim to support it. Exhaustive searching has not turned up some basic answers... experimentation can only say so much... please help: 1) Can I reliably save and retrieve a C# double from a SQL Server float? Tests work okay so far. 2) Can I reliably save and retrieve Double.PositiveInfinity and Double.Negative Infinity from a SQL Server float? Again, tests have worked... but can I count on it? 3) Can I reliably save and retrieve Double.NaN from a SQL Server float column? 4) Can I reliably search on a C# double value within a SQL Server float (assuming I am indeed searching on exactly the double value that I saved into the field)? 5) Can I reliably search for a Double.PositiveInfinity within a SQL Server float column? 6) Can I search WHERE Double.NegativeInfinity < float and float < Double.PositiveInfinity? 7) Is there any way to search / WHERE on Double.NaN (given NaN == NaN returns false, I'd need an IsNaN test in the WHERE clause)?? If not, what's the best way to save double-precision IEEE floating point values in a SQL Server database? Consume the largest and two smallest values in the range specified by SQL Server and dub them +oo, -oo, and NaN, and convert them going in and coming out? (Ick.) Thanks!!
I am aware of the issues with floating point and of Decimal/Numeric.
However, this is not a financial app... its an engineering app, where the range of IEEE floating point is expected... where calculation speed will be critical... where I really would like to use infinity... and where compatibility/consistency with Excel will be important (Excel uses IEEE floating point, without infinity). So, given my app needs to use double, how can my app best use SQL Server?
[quoted text, click to view]
> Perhaps I took too many math classes, but how the hell can a public static > final field in a .NET class/struct ever give you a precise value for =B1 > infinity?!? The same way you were able to write oo (infinity) in those math classes and it meant precisely infinity. There is a bit pattern in IEEE floating point that means "+infinity"... and another that means "-infinity"... and IEEE floating point arithmetic is defined to properly handle both +infinity and -infinity. For example, 5*infinity is infinity.
[quoted text, click to view]
> where calculation speed > will be critical... where I really would like to use infinity... 1) It depends on indexes defined on the table ( server side issue) 2) What did you mean by infinity? If it is an engineering app does it mean that you can accept inaccurate values? "TargetedConvergence@newsgroup.nospam" <junk@EveryBody-Fits.com> wrote in message news:1120023215.353895.314950@g44g2000cwa.googlegroups.com... [quoted text, click to view] > I am aware of the issues with floating point and of Decimal/Numeric. > However, this is not a financial app... its an engineering app, where > the range of IEEE floating point is expected... where calculation speed > will be critical... where I really would like to use infinity... and > where compatibility/consistency with Excel will be important (Excel > uses IEEE floating point, without infinity). > > So, given my app needs to use double, how can my app best use SQL > Server? >
[quoted text, click to view]
"TargetedConvergence@newsgroup.nospam" wrote: > I am writing an engineering solution on top of Office and SQL Server. > Dealing with IEEE double-precision floating point is always a pain, > even with software tools that support it. It appears SQL Server does > not even claim to support it. Exhaustive searching has not turned up > some basic answers... experimentation can only say so much... please > help: > > 1) Can I reliably save and retrieve a C# double from a SQL Server > float? Tests work okay so far. > 2) Can I reliably save and retrieve Double.PositiveInfinity and > Double.Negative Infinity from a SQL Server float? Again, tests have > worked... but can I count on it? > 3) Can I reliably save and retrieve Double.NaN from a SQL Server float > column? > 4) Can I reliably search on a C# double value within a SQL Server float > (assuming I am indeed searching on exactly the double value that I > saved into the field)? > 5) Can I reliably search for a Double.PositiveInfinity within a SQL > Server float column? > 6) Can I search WHERE Double.NegativeInfinity < float and float < > Double.PositiveInfinity? > 7) Is there any way to search / WHERE on Double.NaN (given NaN == NaN > returns false, I'd need an IsNaN test in the WHERE clause)?? > > If not, what's the best way to save double-precision IEEE floating > point values in a SQL Server database? > Consume the largest and two smallest values in the range specified by > SQL Server and dub them +oo, -oo, and NaN, and convert them going in > and coming out? (Ick.) > > Thanks!! According to BOL under the entry for the float data type, float[n] follows the SQL-92 standard where n is 1-53. And then the article titled "Using decimal, float, and real Data", BOL states... <quote> The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types </quote> .... <quote> The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Serve™ uses round up </quote> I have no idea about your Nan and infinity in SQL queries, but I would assume (regarding question #4) that you would use SQL similar to the canonical way you compare FP's in procedural languages... SELECT blah WHERE fld BETWEN @target - @precesion AND @target + @precision Craig
Hi
Don't use float datatype in SQL Server . It is not accurate datatype, instead use DECIMAL/NUMERIC datatype See this article http://www.aspfaq.com/show.asp?id=2477 "TargetedConvergence@newsgroup.nospam" <junk@EveryBody-Fits.com> wrote in message news:1120020009.850703.85170@g14g2000cwa.googlegroups.com... [quoted text, click to view] > I am writing an engineering solution on top of Office and SQL Server. > Dealing with IEEE double-precision floating point is always a pain, > even with software tools that support it. It appears SQL Server does > not even claim to support it. Exhaustive searching has not turned up > some basic answers... experimentation can only say so much... please > help: > > 1) Can I reliably save and retrieve a C# double from a SQL Server > float? Tests work okay so far. > 2) Can I reliably save and retrieve Double.PositiveInfinity and > Double.Negative Infinity from a SQL Server float? Again, tests have > worked... but can I count on it? > 3) Can I reliably save and retrieve Double.NaN from a SQL Server float > column? > 4) Can I reliably search on a C# double value within a SQL Server float > (assuming I am indeed searching on exactly the double value that I > saved into the field)? > 5) Can I reliably search for a Double.PositiveInfinity within a SQL > Server float column? > 6) Can I search WHERE Double.NegativeInfinity < float and float < > Double.PositiveInfinity? > 7) Is there any way to search / WHERE on Double.NaN (given NaN == NaN > returns false, I'd need an IsNaN test in the WHERE clause)?? > > If not, what's the best way to save double-precision IEEE floating > point values in a SQL Server database? > Consume the largest and two smallest values in the range specified by > SQL Server and dub them +oo, -oo, and NaN, and convert them going in > and coming out? (Ick.) > > Thanks!! >
I'd be interested in reading the answers to your questions, if you ever get
them. "TargetedConvergence@newsgroup.nospam" <junk@EveryBody-Fits.com> wrote in message news:1120026479.725686.18790@g14g2000cwa.googlegroups.com... [quoted text, click to view] > Perhaps I took too many math classes, but how the hell can a public static > final field in a .NET class/struct ever give you a precise value for ± > infinity?!? The same way you were able to write oo (infinity) in those math classes and it meant precisely infinity. There is a bit pattern in IEEE floating point that means "+infinity"... and another that means "-infinity"... and IEEE floating point arithmetic is defined to properly handle both +infinity and -infinity. For example, 5*infinity is infinity.
[quoted text, click to view]
> 2) What did you mean by infinity? If it is an engineering app does it > mean > that you can accept inaccurate values? Perhaps I took too many math classes, but how the hell can a public static final field in a .NET class/struct ever give you a precise value for ± infinity?!? -- Peace & happy computing, Mike Labosh, MCSD "Mr. McKittrick, after very careful consideration, I have come to the conclusion that this new system SUCKS." -- General Barringer, "War Games"
Thanks for giving the title of that doc... found it easily... I had
looked at a lot of MSDN docs and none claimed IEEE 754 (MSDN's search function is pathetic)... glad to see it is supported (though at least some of the tools only partially support infinities... it'll print -1.#INF, but you cannot input that). Thanks again. If anybody is aware of any "gotchas" specific to SQL Server, please let me know. (I am fully aware of the "gotchas" for floating point numbers in general.)
[quoted text, click to view]
"TargetedConvergence@newsgroup.nospam" wrote: > Thanks for giving the title of that doc... found it easily... I had > looked at a lot of MSDN docs and none claimed IEEE 754 (MSDN's search > function is pathetic)... <snip> You're welcome. BTW, I've noticed that working with a local copy of the MSDN docs seems to require some kind of bizarre 6th sense for using the correct search terms in many cases <g>, and I've given up on using the online search at msdn.microsoft.com... I just use google.com/micrsoft :) Craig
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |