Prevent mammoth numerical errors by forcing SQL Server to treat values as decimals

What's 2 divided by 3? Anyone for 0 as the answer? Unfortunately, that's what you may get if you're not fully aware of how SQL Server treats numbers in queries.

For example, try running the following query:

SELECT 2/3

And the winner is ...

Zero. The reason SQL Server returns 0 should be pretty obvious in this example. Both 2 and 3 are integers; therefore, SQL Server assumes the result should be an integer. Hence, it rounds down to the nearest integer, which is 0.

Unfortunately, figuring out that this issue was the culprit may not be so easy with a more complicated expression inside a complex application. Instead, your end result may be something quite bizarre. Worse still, nobody may even question the critically wrong result.

A quick and dirty way to fix this problem is to force SQL Server to think in decimals by adding trailing zeros, like this:

SELECT 2.0/3.0

In fact, you only have to do this with either one of the two operands, and SQL Server will still return the same value: .666666. (You could call this math problem "the Devil's division.") Add several more zeros after the decimal, and SQL Server will reserve more memory and thus return more sixes after the decimal.

One advantage of adding trailing zeros is that it lets you avoid bogging down your queries with too many queries. When generating SQL, your application code can add the zeros automatically. (Just make sure it never adds zeros when there's no decimal!)

Of course, the alternative is to use the CAST() or CONVERT() functions to convert to data types such as decimal, float, or real, like so:

SELECT cast(2 as decimal) /cast(3 as decimal)

Curiously, when you use the CAST() function for both numbers, SQL Server returns .6666666666666666666, whereas using it for only one of them still gives you just 6 sixes. So just be aware that SQL Server calculates precision a little differently depending on whether you use trailing zeros or functions. Print this post