On projects in the past I’ve been in situations where I needed to decide between using a approximate data types (Float and Real) or a precise data types (Numeric and Decimal). If you look up the descriptions of these on msdn you will find that decimal and numeric data types are functionally equivalent and have a fixed precision and scale. This means that during design you must anticipate and define the maximum total digits that will be used, as well as the maximum decimal places to use. While this may be perceived as a potential downfall by not being as flexible, they do make up for this in the fact that they are very accurate.

On the other hand you have float and real which are quite flexible. You do not need to define the precision or scale. However, they are not nearly as accurate. But that is where the question arises; what does it mean to not be accurate? Let’s take a look.

Let’s run the query below and take a look at the difference it makes on aggregations.

use AdventureWorksDW
GO

select sum(cast(TotalProductCost as float)) as TotalProductCost_Float
, sum(cast(TotalProductCost as real)) as TotalProductCost_Real
, sum(cast(TotalProductCost as decimal(15,4))) as TotalProductCost_Decimal
, FloatDifference = sum(cast(TotalProductCost as decimal(15,4))) - sum(cast(TotalProductCost as float))
, RealDifference = sum(cast(TotalProductCost as decimal(15,4))) - sum(cast(TotalProductCost as real))
from FactInternetSales


Here are the results.

As you can see the float and real values are are indeed different when compared to the decimal values. Keep in mind that this is a relatively small amount of records (60,000) and the more data you have, the larger the variance will be. In our original data, the values only have a maximum of four decimal places. However, on our query the float and real values somehow end up with either seven or eight decimal places.

Ok, let’s have some more fun.
In this query, we will pick some values and add them up to see what happens.

DECLARE @TestValue1 as nvarchar(80)
DECLARE @TestValue2 as nvarchar(80)
set @TestValue1 = '1111.1114'
set @TestValue2 = '1111.1114'

select
cast(@TestValue1 as float) + cast(@TestValue2 as float) as FloatSum
,cast(@TestValue1 as real) + cast(@TestValue2 as real) as RealSum -- drops a decimal value and rounds
,cast(@TestValue1 as decimal(15, 4)) + cast(@TestValue2 as decimal(15, 4)) as DecimalSum

On this query, you can see that the real data type starts to quickly fall apart. It drops a decimal place and rounds up.

On this query we will add to the precision and scale to see what happens.

DECLARE @TestValue3 as nvarchar(80)
DECLARE @TestValue4 as nvarchar(80)
set @TestValue3 = '111111.11111'
set @TestValue4 = '111111.11111'

select
cast(@TestValue3 as float) + cast(@TestValue4 as float) as FloatSum
,cast(@TestValue3 as real) + cast(@TestValue4 as real) as RealSum
,cast(@TestValue3 as decimal(15, 4)) + cast(@TestValue4 as decimal(15, 4)) as DecimalSum

On this query, float is somehow adds another decimal value. Real is also having issues. Somehow we lost 3 decimal places on the real value.

Alright, let’s try one more. This time we will change the values a bit.

DECLARE @TestValue5 as nvarchar(80)
DECLARE @TestValue6 as nvarchar(80)
set @TestValue5 = '1111111.44444'
set @TestValue6 = '1111111.44444'

select
cast(@TestValue5 as float) + cast(@TestValue6 as float) as FloatSum
,cast(@TestValue5 as real) + cast(@TestValue6 as real) as RealSum
,cast(@TestValue5 as decimal(15, 5)) + cast(@TestValue6 as decimal(15, 5)) as DecimalSum

On this query, we found the tipping for real as we lost all decimal places and it decided to round up again. However, float managed to pull through successfully.

As you can see your mileage may vary with float and real but if you are looking for accurate calculations you would be well advised to use either decimal or numeric!