SQL Server float datatype

Question

The documentation for SQL Server Float says

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Which is what I expected it to say. If that is the case though why does the following return 'Yes' in SQL Server (unexpected)

DECLARE @D float
DECLARE @E float

set @D = 0.1
set @E = 0.5

IF ((@D + @D + @D + @D +@D) = @E)
 BEGIN
 PRINT 'YES'
 END 
ELSE
 BEGIN
 PRINT 'NO'
 END

but the equivalent C++ program returns "No" (expected as 0.1 can't be represented exactly but 0.5 can)

#include <iostream>

using namespace std;

int main()
{
float d = 0.1F;
float e = 0.5F;

if((d+d+d+d+d) == e)
 {
 cout << "Yes";
 }
else
 {
 cout << "No";
 }
}

Malte Clasen · Accepted Answer · 2010-05-30 12:17:10Z

8

This might be caused by different rounding strategies, the order in which operands are applied, the way intermediate results are stored and much more. See What Every Computer Scientist Should Know About Floating-Point Arithmetic for details. If you are not absolutely sure you know what you are doing, you should avoid equality comparisons in floating point number computations.

answered May 30, 2010 at 12:17

Malte Clasen

5,6571 gold badge25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

+1 I think your first point on rounding strategies may well have been bang on the money. According to msdn.microsoft.com/en-us/library/ms187912.aspx "The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up.".

Community · Accepted Answer · 2020-06-20 09:12:55Z

Rather than adding a new answer, I would like to add some quotes from technet.microsoft.com - Using decimal, float, and real Data

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.

Also, as you know, the intention is described below:

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up.

About Decimal and Numeric

Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified. In Transact-SQL, numeric is functionally equivalent to the decimal data type.

Collectives™ on Stack Overflow

SQL Server float datatype

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related