Problem Children

Sometimes things just don't work the way you expect. Have you ever walked toward a door, pushed hard to open it, and—pow!—slammed right into it? The door opened the other direction, of course. Some SQL Server data types—including integer, character, numeric, datetime, and uniqueidentifier—also behave in ways that might surprise you. Let's look at some of these behaviors and how you can keep them under control.

The Integer Problem
Let's say your company charges $2 per pound to deliver small packages. You store the package weight in ounces as an integer in your database. To calculate how much to charge a customer named Bob for delivering his 8-ounce package, you might use a SELECT statement like this:

SELECT package_weight / 16 * $2.00

Bob should owe $1, right? You can use the following script to run a quick check of this answer without even creating a table:

DECLARE @package_weight int
SELECT @package_weight = 8  —Bob's package weighed 8 ounces
SELECT @package_weight / 16  * $2.00

This code's result shows that Bob gets his package delivered for free (.0000). Now, increase the package weight to 16 ounces. At 16 ounces, Bob will owe your company $2; at less than 16 ounces, he'll owe nothing.

The problem here is that SQL Server keeps intermediate results in the same data type as the operands in an equation. In this case, both operands (8 and 16) are integers. Using integer math, 8 divided by 16 equals 0 (with a remainder of 8), and 0 multiplied by $2 is still 0.

If the two operands have differing data types, SQL Server converts the intermediate and final result to the higher precedence data type. Figure 1 shows the precedence order for SQL Server 2000 data types. For example, dividing an integer by a decimal results in a decimal because decimal is higher than integer in precedence. You can take advantage of this precedence in your delivery problem by changing @package_weight to a decimal:

DECLARE @package_weight decimal(6,2)
SELECT @package_weight = 8  —Bob's package weighed 
8 ounces
SELECT @package_weight / 16  * $2.00

Now you get the answer you need to stay in business: Bob owes $1 for his 8-ounce package.

If you can't or don't want to change the package_weight column's data type, you can use the CAST() or CONVERT() function to convert the column to a decimal:

SELECT CONVERT(decimal(6,2),package_weight) / 16 * $2.00 FROM packages

or

SELECT CAST(package_weight AS decimal(6,2)) / 16 * $2.00 FROM
packages

Alternatively, you can use a shorthand way to get the correct answer to the problem. Perhaps you've seen some mystery code that looks like this:

SELECT (package_weight * 1.) / 16  * $2.00 FROM ...

Multiplying package_weight by 1. yields a decimal result. Note that the period after the 1 is required to define a number as a decimal. Or, because the second operator in this example is a literal, you could merely add a decimal point after the second operator:

SELECT package_weight / 16. * $2.00

Either way works fine, as long as one of the operands in the division equation can contain a fraction.