Scaling and precision Issues to be aware of when using math in transact SQL

By on in , with No Comments

When doing math in transact SQL for SQL Server there are a number of rules regarding scaling and precision that you need to be aware of.

For example in the following code:

   SELECT 10/100

You may expect the value 0.1 to be returned by the query, but that’s not what’s returned. Instead you get the following value returned:

Division in sql that doesn't return a decimal.

Division in sql that doesn’t return a decimal.

This is because transact SQL uses the scale and precision of the operands to figure out the scale and precision for the returned value, so if you have no decimal places in your operands your result will have no decimals. To fix this example all you need to do is convert one of the numbers to a datatype that contains a decimal.

Division with the correct decimals

Division with the correct decimals

There are actually a set of rules that SQL Server uses for each operation type which you can find here that determine the precision and scale of the result. So next time you see something “funny” going on with your math in SQL make sure that you check the precision and scaling rules to make sure you get the answers you are looking for.

« »