Decimal Conversions in SQL Server Change Datatype Format

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:

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:

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.

This behavior is not unique to MS SQL, to SQL 92 or anyother version of SQL. At one time all programmers had to worry about whether to choose Integer, Fixed Point (Numeric, Decimal) or Floating Point (Float, Real) for calculations. Most compliers had machine specific extensions that faciliated access to the CPUs math functions.

We all had to be aware of the general rules for precesion and scale. You did not want to be the one who caused overflow, additive cancelation, or any of a number of related problems.

Donald Knuth's "The Art of Computer Programming" is a good reference if you want to uderstand the intracies of Fixed and Floating point math.

This is one of the short comings of modern programmer training. Everyone just assumes that all math works exactly the same as it does on the calculator or in Excel.

The big picture is that not everyone is aware of the need for explicit datatype definitions when using decimals and MS SQL. It was a topic that came across my desk frequently enough at my old job that I thought it would be worth writing down and submitting as a tip.

When writing the tip I hadn't thought about explicitly defining the result, thanks for pointing that out to me.