SQL Server Performance

Handling Infinity in SQL

Discussion in 'SQL Server 2005 General Developer Questions' started by R J, Mar 5, 2008.

  1. R J New Member

    In my database, I have some float fields and our application inserted some rows with "infinity" as the value for these columns. Now we have trouble reading this data from the database, and even SQL Management Studio gives us an "arithmetic overflow" error. I can cast the colum as varchar, so I get 1.#INF or -1.#INF, but I cannot filter, sort, or do anything with this column or else I get the arithmetic overflow error.
    So far, I am reading the data into a table variable (casting the troubled columns as varchar), then filtering (LIKE '%inf%') to figure out what rows have the incorrect data, and then fixing from there. I was wondering if anyone had any tricks on how to do this differently. I would like to avoid using the table variable, but can't see how I can avoid the arithmetic overflow error. Any help would be appreciated.
  2. ranjitjain New Member

    Are you using mySQL, because in sql 2005 I'm trying to select infinite number and it throws error as "out of the range of computer representation (8 bytes)". I tried select 1e500.
    Can you post such sample data to create such scenario i.e. to reproduce this issue
  3. R J New Member

    We are using SQL 2005, but compatibility mode is for SQL 2000 for this particular database. I suppose that could make a difference.
    I wish I knew what the data was. I have a feeling the problem is caused when we insert a decimal value where the decimals continue indefinitely. For example 2 divided by 3. There are cases where some averaging is done, so I may have to round off some of these values before saving back to the database.
    Do you think it is possible that inserting a value with too many decimal places could cause this problem?
  4. satya Moderator

    Simple concept - if the result is higher than the storage of particular data type it will throw the exception or arithimetic overflow (unless you have suppressed the errors).
    So better to choose correct data type!
  5. R J New Member

    So, which SQL data type is the equivalent of a double in .NET?
  6. satya Moderator

    As documentation:
    An arithmetic overflow is caused by a calculated column value that exceeds the column's specified size. When this error occurs, the calculation stops and the remainder of the results pane is not filled.
    Computed columns are often the cause of arithmetic overflows. For example, consider the case where columns c1, c2, and c3 are defined as a data type of INTEGER. Further, assume that c3 is computed using the formula ([c1] * [c2]). If large values (such as 9999) are entered into c1 and c2, then the computation of c3 might exceed the allowable INTEGER limit. The size limitations for data types are determined by your database specification.

Share This Page