By: Thomas LaRock | Comments (8) | Related: 1 | 2 | > Data Types
Problem
When performing simple calculations inside of SQL Server using decimals, you can observe what may seem as odd behavior with the resulting datatype. Instead of returning the desired decimal format SQL Server generates an entirely different decimal format. In this tip we will look at the issue and how to resolve this.
Solution
To resolve this problem you can use the CAST or CONVERT functions in order to ensure that you are returning the correct datatype. I will walk through an example of the issue and how this can be resolved.
SQL Server provides many different datatypes for your use. Many people who are new to SQL Server may not be aware that depending upon their code they may end up having implicit conversions resulting in unexpected results. For example, the following code is something you might come across at one time or another:
DECLARE @first decimal(18,2), @second decimal(18,2) SET @first = 1234567891234567.87 SET @second = 222.13 SELECT @first/@second as 'Result'
The end result of this code is as follows:
Result 5557862023295.22293251699455273938
The result is no longer a decimal(18,2) datatype. This may or may not be a problem, depending on the nature of the application and what happens after the result is returned. However, this is often the time when I will hear someone comment about how SQL Server "isn't working right" and point to the result and then tell the DBA to "go fix the problem".
The truth is that MS SQL is working exactly as expected, although finding the documentation for this behavior can be difficult. In this particular case you want to examine the MSDN entry for "Precision, Scale, and Length" http://msdn.microsoft.com/en-us/library/ms190476.aspx .
Reviewing the details in the document mentioned above, you will find that the behavior is exactly as expected. In the example above we are doing division of two identical decimal(18,2) datatypes. The table at http://msdn.microsoft.com/en-us/library/ms190476.aspx shows us the formula for division on the fourth line in the table.
In our example we have both p1 and p2 = 18 and both s1 and s2 = 2. That means the precision for our result will be as follows based on the formula found in the MSDN article mentioned above:
p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 - 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39
However, the maximum allowed precision is 38 (look for the note at the bottom of the MSDN article).
Further, the scale for our result will be simply:
max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21
But since we had to reduce our precision by one from 39 to 38, we will also reduce our scale by one from 21 to 20 (again, read that note at the bottom of the MSDN article), therefore the datatype for the result will be decimal(38,20), which is quite different than the decimal(18,2) we started with.
You can avoid issues such as this by using the CAST or CONVERT functions inside of MS SQL Server. You can reference the MSDN article http://msdn.microsoft.com/en-us/library/ms187928.aspx and note the chart in the middle that explains the explicit and implicit conversions actually has an asterisk for the decimal to decimal conversion. They are trying to impress upon you right here that you need to use a CAST or CONVERT function in order to avoid the loss of precision or scale.
So, which one to use? My preference is to use CAST whenever possible, as CONVERT is a function specific to MS SQL Server. That means if you use CAST, your code will be more portable (or, put another way, your code will work against more than one platform). However, CAST cannot be used for formatting purposes, for that you will need to use CONVERT.
In our example, the final code we would want to have is as follows:
DECLARE @first decimal(18,2), @second decimal(18,2) SET @first = 1234567891234567.87 SET @second = 222.13 SELECT CAST(@first/@second as decimal(18,2)) as 'Result'
This will ensure that we have the desired datatype for our result which is:
Result 5557862023295.22
Next Steps
- Verify that you are getting the correct datatype returned when performing decimal conversions and use CAST when possible.
About the author
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
