4

I had created table which hold small floating values using below query

create table temp_data (val float)

But when it gives warning message for very small values (e-314)

insert into temp_pce_rank_data values(2.466987305926227e-314)

Data gets inserted with value '0' warning message is: The floating point value '2.466987305926227e-314' is out of the range of computer representation (8 bytes).

How I can store & retirve very small values (E-314) in SQL Server database? Thanks

7
  • 4
    Do you need to do any arithmetic with the number in the database?
    – Philᵀᴹ
    Oct 13, 2016 at 10:31
  • I suspect that you may be using the wrong unit measure. What is the range of values you will be using? Are you planning to store both huge and extremely small values in the same column? Oct 13, 2016 at 14:43
  • @Philᵀᴹ Yes, I want to do arithmetic with numbers.
    – Pravin
    Nov 3, 2016 at 10:30
  • @spaghettidba I'm planning to store only extremely small values in same column
    – Pravin
    Nov 3, 2016 at 10:32
  • 1
    I mean that if you're storing a few milliseconds as years, you will get an extremely small number. Store it as milliseconds and it will be a much more manageable number. Nov 3, 2016 at 12:04

4 Answers 4

7

None of the provided numeric types (float, decimal) are able to store so small numbers. The example you have, 2.466987305926227e-314, is smaller than the smallest normal double-precision float number.

I see two options, if you want to store such numbers:

  • Use a custom solution with two columns, one for the mantissa and another for the exponent. The disadvantage will be that you'll have to do all the math operators and functions with complex expressions (even for addition).

  • Use a custom CLR User-Defined Type. From that page:

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.

Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:

Date, time, currency, and extended numeric types

Geospatial applications

Encoded or encrypted data

4

It is technically possible to store smaller values, the catch seems to be that they need to be the result of a calculation and not a literal value. According to Paul's answer here (also on DBA.StackExchange), What is the actual lowest possible positive REAL number, the true smallest positive value for a FLOAT is: 4.94065645841247E-324. As long as you don't need anything smaller, this should be doable. You just need to make sure to stay within the initial limit of 2.23E-308.

So, you can think of E-307 as being the "limit", and if your exponent is beyond 307, then just make it be 307. Then, either divide that literal by 1.0e+{original_exponent - 307} OR multiply that literal by 1.0e-{original_exponent - 307}.

I'm not sure if there are any computational consequences of doing this, so you should certainly test. The one thing I did notice was a small loss of precision in your original value: the last two digits of 27 got rounded up to be 3.

Test Setup (run once)

CREATE TABLE #FloatTest (ID INT IDENTITY(1, 1), TheFloat FLOAT);

Test 1 (store value in variable and then in a column)

-- Original value = 2.466987305926227e-314
-- {original_exponent - 307} = 314 - 307 == 7

DECLARE @LessThan FLOAT;

SET @LessThan = 2.466987305926227e-307 / 1.0e+7; -- divide by "plus"
SELECT @LessThan AS [FloatVariableFromDivide];

SET @LessThan = 2.466987305926227e-307 * 1.0e-7; -- multiply by "minus"
SELECT @LessThan AS [FloatVariableFromMultiply];

INSERT INTO #FloatTest ([TheFloat]) VALUES (@LessThan);

SELECT tmp.[TheFloat], tmp.[TheFloat] / 1.0e+5 AS [EvenLesserThan]
FROM   #FloatTest tmp;

Returns:

FloatVariableFromDivide
2.46698730592623E-314


FloatVariableFromMultiply
2.46698730592623E-314


TheFloat                 EvenLesserThan
2.46698730592623E-314    2.46696858281451E-319

Test 2 (grab the value from the table and place in a variable)

DECLARE @FromTable FLOAT;

SELECT @FromTable = tmp.[TheFloat]
FROM   #FloatTest tmp;

SELECT @FromTable AS [FromTableVariable],
       @FromTable / 1.0e+7 AS [EvenMoreLesserer];

Returns:

FromTableVariable        EvenMoreLesserer
2.46698730592623E-314    2.46538757274782E-321
1
  • One point: the more you go towards the 1E-324, the more you lose on accuracy of the stored value. Oct 13, 2016 at 16:54
1

Do it at the application level

Even .NET only goes down to ±5.0 × 10−324

Multiple by 10^300 and insert

After you select you can scale

If you are dividing then you don't need to scale

A * B would be A * B * 10^300 * 10^300 and you are going to be outside the range

You might be better off with BigInt, SmallInt with SmallInt for the exponent

-3

Float has a range of - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 with a maximum data size of 8 bytes. If you really need double precision you should look at Oracle which float datatype can be 1 to 22 bytes.

Check this: https://stackoverflow.com/questions/16900314/what-is-the-best-way-to-recognize-and-convert-these-minimum-double-values-into

3
  • 2
    Ducks and waits for downvotes :)
    – Spörri
    Oct 13, 2016 at 10:32
  • 1
    Postgres can also store 1e-314 value using numeric (or decimal) - the smallest value is 1e-16383
    – user1822
    Oct 13, 2016 at 12:09
  • So this is the mythical case everybody is talking about when advocating abstract DAL ;-)
    – Ekus
    Feb 19, 2020 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.