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
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
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
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
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.
