SQL Expert

About FLOAT datatype:

The float and real data types are known as approximate data types (For example, 0.01 cannot be represented exactly by a floating point number - the closest representation is actually 0.0099999998) . The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Floating points have unexpected irrational numbers.

For instance you can’t store 1/3 as a decimal, it would be 0.3333333333… (and so on)

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

FLOAT data type has a limited precision - its only 15 digits (for comparison - decimal and numeric have maximum precision of 38 digits).

Therefore if you have a number with 13 digits before decimal point and 16 digits after decimal point, you will have only 15 digits stored in the float datatype (out 29)

declare @f float
select @f = 1111146804871.1111222233334444
select @f

———————————–

1111146804871.11

You are loosing 14 digits after decimal point 1111146804871.1111222233334444 which makes float datatype very unprecise especially when you are dealing with big numbers and when decimal point values are significant.

About Deterministic vs Non-deterministic functions:

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.

Non-deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.

Because the data type is inprecise, the result depends on the order in which the values are processed.

Since the order processing is undefined, the result will have a certain amount of uncertainty, it will be somewhere between an upper bound and a lower bound.

If a couple of very small positive values are added first, and then a large positive value is added, then the result will yield to the upper bound. If the processing starts with the large value, and then a couple of small values are added, then they have little “weight” because of the reduced precision, and the result will yield to the lower bound.

Here is an example of why we can not use float:

declare @big float, @small float, @sum1 float, @sum2 float, @sum3 float, @i INT
SELECT @big = 12345678901234.50, @small = 0.01, @i = 0
SELECT @sum1 = @big, @sum2 = 0, @sum3 = 0
WHILE @i < 5 BEGIN
SELECT @sum1 = @sum1 + @small, @sum2 = @sum2 + @small, @sum3 = @sum3 + @small
SET @i = @i + 1
END
SELECT @sum2 = @sum2 + @big
SELECT @sum1, @sum2, @sum3

———————- ———————- ———————-
12345678901234.5       12345678901234.6       0.05

(1 row(s) affected)

This is why you cannot have sums of floats in indexed views - they are not deterministic, they may depend on order of adding.

Here is another example proving floats to be not precise datatype :

declare @i float
select @i =0.1
select @i + @i + @i

In Query Analyzer (Enterprise Manager connected to 2000 or 2005) you get
0.30000000000000004

In SSMS you get
0.3

Any datatype that has rounding issues, can have different results if the order of calculation is changed. That is clearly visible for float, as opposed to decimal, when doing additions with numbers with a finite number of decimal places.

But when doing multiplications, the rounding issues of decimal are much easier to spot than the rounding problems of float:

DECLARE @f1 float, @f2 float, @f3 float;
DECLARE @n1 numeric(38,10), @n2 numeric(38,10), @n3 numeric(38,10);
SET @f1 = 123456789.012345;
SET @n1 = 123456789.012345;
SET @f2 = 0.00001;
SET @n2 = 0.00001;
SET @f3 = 10000.0;
SET @n3 = 10000.0;
SELECT Float1 = (@f1 * @f2) * @f3, Float2 = (@f1 * @f3) * @f2;
SELECT Numeric1 = (@n1 * @n2) * @n3, Numeric2 = (@n1 * @n3) * @n2;

Double datatype also has problems with rounding:

Example 1:

double a = 1.0;
double b = 13.0;
double c = a / b;

I get c equals to 0.076923076923076927 which isn’t what I expected as 0.076923076923076923.

Example 2:

double x = 97.0;
double y = 0.2;
double z = x * y;

I get c equals to 19.400000000000002 which isn’t what I expected as 19.4

Floating point numbers have limited precision, even though the double type has significantly better precision than float (single). When you convert the numbers to strings in order to inspect them, you must often specify the presentation precision or number of decimal places you want, otherwise the conversion will attempt to approximate the binary representation to a decimal one, yielding for instance 19.4000…2, often with the result that the trailing decimals drift from the value calculated on more precise devices.

Let’s expand on it:

1/13 doesn’t equal 0.076923076923076927; it equals 0.076923076923076923, where the highlighted 076923 repeats forever.  Any representation as a decimal fraction with a fixed number of digits must be inexact.

In decimal, only fractions whose denominators have only factors of 2 and 5 have exact terminating representations.

In binary, which is used for single and double precision floating point, only fractions whose denominators are powers of 2 have exact representations.

In the second example, 0.2 has a terminating representation in decimal, since it’s 1/5.

However, since 5 isn’t a power of 2, it doesn’t have an exact representation as a double precision floating point number.

Floats are actually stored as a binary value and a power of 2 exponent.

So 1.5 is stored as 3 x 2 to the -1 (or 3/2)

Using these base-2 exponents create some odd irrational numbers, for instance:

Convert 1.1 to a float and then convert it back again, your result will be something like: 1.0999999999989

This is because the binary representation of 1.1 is actually 154811237190861 x 2^-47, more than a double can handle.