Watch out for imprecise floating point values in your SQL Server data

Two SQL Server data types that are often used to represent numeric values with a fractional (that is, decimal) component—float and real—can get you into trouble if you aren’t paying attention. In SQL Server, floating point values are approximate—and shouldn’t be used if you need absolute precision. Surprised? Don’t feel bad—many DBA’s are unaware of this, but a simple experiment proves the point.

Try running the following query and you’ll see what we’re talking about:

DECLARE @TestValue float — or real

SET @TestValue = 6.9

SELECT TestValue = @TestValue

And you won’t see the result you might expect (6.9). Rather, you see 6.9000000000000004 for float or 6.9000001 for real. (Remember that real is just a synonym for float(24)).

Why does this occur? According to SQL BOL, floating point data is approximate by design and "not all values in the data type range can be precisely represented." So what do you do when you need precise fractional values? Use decimal instead.