Scaling and precision Issues to be aware of when using math in transact SQL
By Mike Griffith on in Programming, Tips 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:
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:
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.
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.