When I'm looking at a database schema for the first time, there are a number of tell-tale signs that give me the hint that the developers really haven't done much work with SQL Server before. They've made a "newbie" mistake.

**One of those is the extensive use of the float data type.**

Most times that I see this, the developers have come from a C or Java background and they assume that something that needs a decimal point in it, needs to be float. There are some situations where float makes sense, but 99% of the time what they should have used was decimal.

**float** is used to store approximate values, not exact values. It has a precision from 1 to 53 digits.

**real** is similar but is an IEEE standard floating point value, equivalent to float(24).

**Neither should be used for storing monetary values.** Because the values cannot be stored precisely, people who use float end up with values that don't match, columns of values that don't quite add up, or totals that are a cent out, etc. They spend their lives trying to round values to fix the issue, and usually don't get it right.

Here's an example. What values should this code print?

You'd expect the values 0.0, 0.1, 0.2 and so on up to 10.0. But that's not what you get. The query would continue until the maximum value for the data type was exceeded (a long time). If you stop the query, you'll see odd values:

Worse, note that our stop value of 10 is actually shown, but it didn't stop:

The problem is that while the value 10 can be stored accurately in float, the value 0.1 can't be. In decimal, we have recurring fractions. 1/3 is 0.33333 recurring. We can't write it precisely in decimal. In binary though, 0.1 has the same issue.

So even though we had a test of WHILE @Value <> 10.0, the value never exactly equalled 10.0. So why does it show 10 in the Messages tab? That's because SQL Server Management Studio (SSMS) rounds the values that it prints. It's not showing us the actual value.

We could fix this by substracting @Value from 10 and taking the absolute value of the result, then comparing it to a small increment. But who wants to write code like that?

In SQL Server, decimal, numeric, money, and smallmoney are the data types with decimal places that store values precisely. numeric is basically a synonym for decimal. money and smallmoney are old Sybase data types that have fixed scale, and have a funky relationship with currency symbols when converting strings. I generally don't use those. (There are some arguments for them in gigantic data warehouses where their smaller storage size might help but with row compression, the reasons for that are quickly disappearing). It's one of the problems with backwards compatibility in SQL Server.

**You can't blame people for using a data type called money for storing amounts of money. But it's generally not the right answer.**

Let's now look at the query from before if we change to decimal:

When executed, it stops exactly as expected:

Decimal (and numeric) require a precision and a scale. These should be chosen appropriately to store the values that you need. You need to keep rounding in mind when you calculate decimal values.

**As I mentioned earlier, there are places where float and/or real make sense, but they are typically scientific calculations, not business calculations.**

FLOATs are surely appropriate for exchange rates (used to convert an amount from one currency to another), because the exchange rate is an approximation. Your article implies they are never appropriate for business calculations.

Hi Michael,

No actually. In most financial organizations that I work in, exchange rates are calculated and stored to a particular number of decimal places, and there are rounding rules that need to be applied when performing calculations on them. The problem with float is that it can't store even simple values accurately. If I say that an exchange rate is 0.1, I want it to be 0.1 not 0.9999 recurring. Here's a simple example of the issue with float:

DECLARE @Value float = 0;

DECLARE @ExchangeRate float = 0.1;

WHILE @Value != 10

BEGIN

SET @Value = @Value + @ExchangeRate;

PRINT @Value;

END;

Ask yourself how many values that would print, then try it.

The point is that if you want an exchange rate to be 0.1, you actually want 0.1, not a number that's approximately 0.1.

When to use FLOAT in real-life scenario?

Hi Edgar, typically when storing scientific values, rather than business values.

Well done in explaining the difference of these data types. Ive read different articles regarding and this is the clearest of all!

Glad that it helped.

DECLARE @CONVERSION float

set @CONVERSION=2.20462442018377

SELECT (@CONVERSION*10.25)

DECLARE @CONVERSION1 decimal

set @CONVERSION1=2.20462442018377

SELECT (@CONVERSION1*10.25)

see the difference b/w output values

i am confused which one i need to choose

Whenever you work with decimal values, you need to decide what the appropriate precision is, rather than just storing it as an approximate value. For example, see the difference if you used decimal(38,20) instead of just decimal.

Total Legend. You are a rock star

You are most welcome

Hi Greg,

I understand what could be the benefit of using fields with type decimals (mainly the possibility to index them), but I think you did not choose your examples objectively. See the following examples (which are not objective either).

I remember also that we chose to go from DECIMAL to FLOAT many years ago precisely because some of our customers complained because the sum of periodized costs per month did not always match the whole cost (per year) with DECIMAL, while it did with FLOAT…

If you add the fact that when using your database with Microsoft Entity Framework, you need to cast all your decimal fields to double (which is the standard type of float variables in most of programming languages) to be able to do proper calculations, use 'M' suffix to initialize them, …, I am not quite sure it is worth.

DECLARE @CONVERSION1 decimal

set @CONVERSION1=1.0

SELECT (@CONVERSION1/3)*3

DECLARE @CONVERSION float

set @CONVERSION=1.0

SELECT (@CONVERSION/3)*3

and your first example with the counter, try running the following one, and see which one works…

DECLARE @Value decimal(10,2)=0.9

WHILE @Value/3*3 1.0

BEGIN

PRINT @Value;

SET @Value+=0.1;

END;

DECLARE @Value float=0.9

WHILE @Value/3*3 1.0

BEGIN

PRINT @Value;

SET @Value+=0.1;

END;

Regards,

Leto

Hi Leto,

While there are examples where taking a value, and dividing by a proportion is going to finally total closer to the original amount, that's not an argument for storing values as approximate values. As I said, you need to store values appropriately and manage rounding. For example, if I need to pay someone $100 quarterly, and send them 1/3 of that each month, I can't actually send them $33.33333333333333333333333333 each month, even though it would total to close to the right value at the end. I need to send them $33.33 (rounded to the nearest cent) for each of the first two months, and $33.34 for the final month. All that takes is knowing what the final amount should be, and deducting the rounded amounts already deducted. Each monetary value is then still precise.

Storing approximate values is not the answer when dealing with money.

As for Entity Framework, it has so many limitations that I don't believe it should be used in serious applications, at least not at scale. In my consulting work, I see an amazing number of issues caused by people using it, and even an amazing number of problems that people have in using it in the first place, once they get past the trivial applications of it. I see a lot of people who finally realise this and remove it (painfully) from their code bases. There are many decisions that its designers have taken for you under the covers; many of which are not sound.

Regards,

Greg

To be precise float (n) – is the number of bits that are used to store the mantissa. It has no nothing in common in that you wrote. While loop trick is also not honest. If you are storing value as decimal (18,2) it says that scale is 2, and in case of float it might be 18 or higher. So in this case my float value will be much more precise compare to your decimal. To stop infinite loop just add CONVERT statement because you are comparing different datatypes. This article is not applicable to any business area.

I hear what you are saying but I completely disagree.