Double.NaN and SQL float data type

  • Thread starter Thread starter Guogang
  • Start date Start date
G

Guogang

Hi,

I use C# to fill a data field of "float" data type in SQL 2000 server. When
I use Double.NaN value, there seems to be a problem in "Query Analyzer". I
can no longer select the row, an error message: "Numeric value out of
range". However, in Enterprise Manager, I can still see the row with the
"float" data field of value: "-1.#IND".

My question: is this just a bug/flaw in query analyzer, or I am not supposed
to assign Double.NaN to a "float" data type in SQL 2000?

Thanks,
Guogang
 
Hi,

NAN is 'not a number'.
You should correct your code to not produce such 'values'.
NAN's can be produced for example by numeric overflow and underflow.
If you tried to store a NAN, which would it be? Overflow or underflow?

If you want null, use null.

See SET ARITH_ABORT

- Tim
 
Guogang,

Although Enterprise Manager can display NaN values, they are not a
supported feature of SQL Server. They can get into tables from sources
other than T-SQL, and this can cause problems. While T-SQL prevents
casts from varbinary to float, they can be inserted from non-T-SQL code
and through DTS imports of data that contains them. The behavior you
see in Query Analyzer is not a bug, and if you need to deal with them in
a SQL Server table, you can identify the infinity versions of NaN values
with a query like

select * from T
where 1/floatColumn = 0

My guess is that arithmetic on floats defaults to the underlying machine
arithmetic, which does support the full IEEE 754 standard.

You can then delete the rows or move them elsewhere, replacing the float
column with NULL or a string value that represents the NaN. If they are
important, one possibility would be to use a second column and have a
two column value like (NULL, '-Infinity') represent a NaN, or use a
sql_variant column.

-- Steve Kass
-- Drew University
-- Ref: 72BD0430-C4FB-4901-8C8D-C3A7B96147BE
 
Back
Top