In this post I provide some examples as to why FLOAT isn’t the best option for storing exact financial data in SQL Server.
During the last couple of years I’ve seen quite a few cases of FLOAT being used to store financial data in SQL Server.
This isn’t ideal due to how the FLOAT data type behaves in SQL Server, and the official documentation makes a clear note of that.
Because of the approximate nature of the float and real data types, don’t use these data types when exact numeric behavior is required. Examples that require precise numeric values are financial or business data, operations involving rounding, or equality checks. In those cases, use the integer, decimal, numeric, money, or smallmoney data types.
Examples
What’s the point of making a post about this if I can’t provide a few examples?
The classical 0.1+0.2<>0.3 example
This or a variation of it is generally the go-to example folks use to point out that FLOAT isn’t great for storing exact values in SQL Server.
| 1 2 3 4 5 6 7 8 9 10 11 | DECLARE @a FLOAT = 0.1, @b FLOAT = 0.2, @c FLOAT = 0.3; SELECT @a + @b AS [Sum], @c AS [Expected], CASE WHEN @a + @b = @c THEN 'Yup' ELSE 'Nope' END AS [Sum=Expected?]; |
Now, if you’re used to how FLOAT behaves in Oracle, which compensates for this*, you might expect the result to be “Yup”, but that’s not the case here.
*Oracle stores FLOAT as a sub-type of its NUMBER data type, which uses Binary Coded Decimal (BCD) internally.
This allows Oracle’s FLOAT to avoid many of the precision issues seen in SQL Server’s FLOAT. Meaning that it behaves more like SQL Server’s DECIMAL/NUMERIC for most practical purposes.
But maybe this example isn’t considered realistic enough.
A bit more realistic
This is closer to what might happen in a sales/e-commerce database.
| 1 2 3 4 5 6 7 8 9 10 11 | DECLARE @ProductPrice FLOAT = 19.99, @Quantity INT = 100, @Total FLOAT = 1999.00; SELECT @ProductPrice * @Quantity AS [CalculatedTotal], @Total AS [ExpectedTotal], CASE WHEN @ProductPrice * @Quantity = @Total THEN 'Yup' ELSE 'Nope' END AS [CalculatedTotal=ExpectedTotal?]; |
But the behavior is the same.
And that’s not all.
FLOAT inconsistencies sneak up on you over many rows
Let’s say that you have a financial reporting platform which generates reports for financial data that spans thousands of rows.
Something like what you’d see when doing monthly or quarterly reporting.
First, we’ll need a table.
| 1 2 3 4 5 | CREATE TABLE #Financials ( Id INT IDENTITY, AmountFloat FLOAT, AmountDec DECIMAL(38,12) ); |
The demo table has both a FLOAT and a DECIMAL column to better demonstrate the difference between approximate and exact numeric data types.
Now to add 10k rows.
| 1 2 3 4 | INSERT INTO #Financials (AmountFloat, AmountDec) SELECT TOP(10000) 0.01, 0.01 FROM sys.columns c1 CROSS JOIN sys.columns c2; |
And to see how the how the sums of those rows look.
| 1 2 3 4 5 6 | SELECT SUM(AmountFloat) AS [FloatSum], SUM(AmountDec) AS [DecimalSum], CAST(SUM(AmountFloat) AS DECIMAL(38,12)) - SUM(AmountDec) AS [Difference] FROM #Financials; |
That’s not very accurate, is it?
And that’s not the worst of it.
At this point you’d be tempted to say that the drift is going in a single predictable direction.
So, if 10k rows give us a drift of +0.000000000014 for the FLOAT column, then 20k rows will lead to something like +0.000000000028.
But that’s not really the case.
I re-run the 10k rows INSERT statement and then I run the query again.
This makes FLOAT related errors not just magnitude issues, but also unpredictable in direction.
Meaning that you can’t just compensate for them in your code.
Now, I’m curious what happens at 100k rows, so I add 80k more rows to the table.
| 1 2 3 4 | INSERT INTO #Financials (AmountFloat, AmountDec) SELECT TOP(80000) 0.01, 0.01 FROM sys.columns c1 CROSS JOIN sys.columns c2; |
And re-run the query.
The drift keeps on drifting.
FLOAT isn’t all bad
It just has its specific uses, and finance or other fields that require accuracy aren’t among those specific uses.
FLOAT is generally used for scientific calculations, graphics, and machine learning.
Conclusion
The title.
If you want to read more data type shenanigans, feel free to check out my post on implicit conversions.