Data types can hold pitfalls for folks who are new to the world of database development—and even for more experienced developers. One mistake happens frequently when novice database professionals represent numbers that contain decimal places.
Six SQL Server data types let you represent numbers that aren't whole integers: decimal, numeric, money, smallmoney, float, and real. Float and real are approximate data types. SQL Server Books Online (BOL) says, "Floating point data is approximate; not all values in the data type range can be precisely represented." In this tip, I don't try to explain the ins and outs of floating-point arithmetic; for more information about its intricacies, see David Goldberg's white paper "What Every Computer Scientist Should Know About Floating Point Arithmetic" at http://citeseer.nj.nec.com/cache/papers/cs/11986/http:zSzzSzwww. cs.uiowa.eduzSz~atkinsonzSzm170.dirzSzgoldberg.pdf/goldberg91what.pdf
Let's create some test data and examine how floating-point arithmetic affects that data. First, use the following code to create a table:
INSERT INTO ApproximateData VALUES (6.9, 6.9)
When you execute the SELECT statement
you get the following result set:
(1 row(s) affected)
You inserted values of 6.9 into both columns, but that's not the number the result set shows. As BOL says, the floating-point values aren't precisely represented. However, when you run the following query:
WHERE RealCol = 6.9 AND FloatCol = 6.9
you get the same result set that you got with the previous query. SQL Server is returning data that you might not have intended to ask for. The WHERE clause contains a search argument that includes an equality operator that says the columns must equal 6.9. However, the result set shows values that are slightly different from 6.9. This anomaly occurs because SQL Server's rules of data type precedence specify that a decimal value such as 6.9 is implicitly converted to a float or real data type (as the case requires) before SQL Server compares the value 6.9 to the column in the WHERE clause. (For more information about how data types work, see the BOL topic "Data Type Precedence.")
Let's look at another example of what can go wrong when you're working with approximate data types. When you run the query
you get the following result:
(1 row(s) affected)
Logically, you expect the result to be 0, but because the numbers are approximate, you end up with a number close to 0 but not exactly 0. The fact that Goldberg's white paper is 75 pages long should suggest that this cursory examination of the float and real data types merely scratches the topic's surface. Just remember that you might not get the numbers you expect when dealing with float and real data types.
Should you avoid float and real data types? In general, approximate data types consume less storage space and can be processed more efficiently than precise numbers such as the decimal data type. If you want to quickly process massive amounts of data, the float or real data type can be helpful. Also, approximate numbers can represent very small or very large values that no other data type can represent, so they're useful for certain types of scientific calculations. But BOL cautions, "Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks." I never use float or real data types in business applications. Even if I understand the limitations of these data types, I can't guarantee that the developers who work with the database also understand.