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.
Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry in roles including programmer, developer, analyst, and database administrator.
LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010. While at Confio, his research and experience helped to create the initial versions of the software now known as SolarWinds Database Performance Analyzer. LaRock joined the SolarWinds family through the acquisition of Confio in 2013.
LaRock is also the Immediate Past President of the Professional Association for SQL Server (PASS) and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses his time working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle, MySQL, SAP, and DB2, making it his mission to give IT and data professionals longer weekends.
