Why is a SQL float different from a C# float

Question

Howdy, I have a DataRow pulled out of a DataTable from a DataSet. I am accessing a column that is defined in SQL as a float datatype. I am trying to assign that value to a local variable (c# float datatype) but am getting an InvalidCastExecption

DataRow exercise = _exerciseDataSet.Exercise.FindByExerciseID(65);
_AccelLimit = (float)exercise["DefaultAccelLimit"];  

Now, playing around with this I did make it work but it did not make any sense and it didn't feel right.

_AccelLimit = (float)(double)exercise["DefaultAccelLimit"];

Can anyone explain what I am missing here?

Solution 1

A SQL float is a double according to the documentation for SQLDbType.

Solution 2

A float in SQL is a Double in the CLR (C#/VB). There's a table of SQL data types with the CLR equivalents on MSDN.

Solution 3

And normally you would never want to use float in SQL Server (or real) if you plan to perform math calculations on the data as it is an inexact datatype and it will introduce calculation errors. Use a decimal datatype instead if you need precision.

Solution 4

The float in Microsoft SQL Server is equivalent to a Double in C#. The reason for this is that a floating-point number can only approximate a decimal number, the precision of a floating-point number determines how accurately that number approximates a decimal number. The Double type represents a double-precision 64-bit floating-point number with values ranging from negative 1.79769313486232e308 to positive 1.79769313486232e308, as well as positive or negative zero, PositiveInfinity, NegativeInfinity, and Not-a-Number (NaN).

View more solutions

Comments

[Alex S]

It is OK for most math and engineering calculations. Now, financial and accounting calculations are, of course, different.

[Alain]

They are similar but certainly not equivalent. Double supports values of +Infinity, -Infinity, and NaN - as per the IEEE specification, but SQL float most definitely does not. and will throw overflow errors.

[Alain]

They are similar but certainly not equivalent. Double supports values of +Infinity, -Infinity, and NaN - as per the IEEE specification, but SQL float most definitely does not. and will throw overflow errors. Hopefully MSDN will add this to their info pages.

[Admin]

Ok. So what's your answer?

[RaoulRubin]

I agree with the comments of Alain May, but I don't yet understand the details of why. I have seen cases where .net double will represent numbers that will break SQL float. Examples include infinity values, and certain negative exponents that approach zero. I suspect MS optimized for storage efficiency.

[and_the_rand]

@RaoulRubin: See the MSDN documentation for TSQL floats. They never mention IEEE compliance and state that it follows some unnamed ISO standard. A quick search yields ISO/IEC 10967 which states it's compatible with IEEE 754 but does not guarantee a full implementation.