{"id":18,"date":"2018-07-28T18:00:03","date_gmt":"2018-07-28T18:00:03","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=18"},"modified":"2018-06-17T17:48:31","modified_gmt":"2018-06-17T17:48:31","slug":"danger-danger-float-value","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/28\/danger-danger-float-value\/","title":{"rendered":"Danger! Danger! Float value!"},"content":{"rendered":"

I’m not saying that floating point numbers are the devil, and I’m not saying that the float data type should be avoided. But they should only be used when it’s entirely necessary and appropriate. The hidden danger of improper use can can become a ticking time bomb!<\/p>\n

Most (if not all) dangers come from FLOATs representing tiny fractions, rather than a value of 0.<\/p>\n

When and why to use a FLOAT<\/h1>\n

\"Three<\/a><\/p>\n

The floating point number is an approximation of a number, it’s true power and use is in fractions. A classic case for fractions in computing is when we divide 1 by three, then add the result to itself three times (three thirds making a whole).<\/p>\n

The below example creates a value of 1 in the CTE. This value is stored in a temporary table, including dividing by three using both implicit and explicit type casting for comparison.<\/p>\n

-- Drop the temp table if it exists\r\nIF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL\r\n    DROP TABLE #tmp_data;\r\n\r\n-- Create a value of 1 (cast as a float)\r\n-- Then divide it by 3, storing the result as DECIMAL and as FLOAT\r\nWITH data_set AS (\r\nSELECT CAST(1 AS FLOAT) AS [value]\r\n)\r\nSELECT [value],\r\n       [value]\/3                        AS [value_d3],\r\n       CAST([value]\/3 AS DECIMAL(10,5)) AS [value_DEC],\r\n       CAST([value]\/3 AS FLOAT)         AS [value_FL]\r\n  INTO #tmp_data\r\n  FROM data_set\r\n;\r\n\r\n-- View the resulting numbers\r\nSELECT *\r\n  FROM #tmp_data\r\n;\r\n<\/pre>\n\n\n\n\n
value<\/th>\nvalue_d3<\/th>\nvalue_DEC<\/th>\nvalue_FL<\/th>\n<\/tr>\n
1<\/td>\n0.3333333333333333<\/td>\n0.33333<\/td>\n0.3333333333333333<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The results in that table will look largely consistent. The key difference between them will only come when we then want to multiply our thirds by three to get 1.<\/p>\n
-- Multiply DECIMAL and FLOAT values by 3\r\nSELECT [value_DEC] * 3 AS [value_DEC_MULT],\r\n       [value_FL]  * 3 AS [value_FL_MULT]\r\n  FROM #tmp_data\r\n;<\/pre>\n\n\n\n\n
value_DEC_MULT<\/th>\nvalue_FL_MULT<\/th>\n<\/tr>\n
0.99999<\/td>\n1<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The result for a DECIMAL value isn’t quite what we might expect. The float gives us a nice tidy 1, but the decimal gives us a little less than 1. This is because the DBMS is able to see that the float is an approximation of a third. But the decimal value is a literal 0.33333.<\/p>\n
So, use a FLOAT when we want a fraction to behave like a fraction. In all other cases, use an INTEGER or DECIMAL datatype.<\/p>\n
It’s worth looking at these examples in SQL Fiddle, SQL Server and Oracle behave the same, but MySQL returns a different result. The DBMS doesn’t realise that it’s looking at a third, and multiplies the fraction leaving us with a small variance. In this instance (and many like it) we can get around the variance by CASTing the result:<\/p>\n