MS SQL, Math, and Decimals

Of course, nothing ever happens at work until you want to log off and head home. Such was the case yesterday. I was shutting down my laptop when I received a phone call from a colleague who needed help with a query. Seems as if the results of their select were returning decimals with a higher scale than desired.

I gave a quick scan of the situation, looking at the query, the stored procedure, and the table structure. And what I found was that the data was intact, the query was fine, and that the results had lots of extra decimals. I told them to simply convert their end results to whatever they were looking for (in this case, they wanted a result of decimal(18,4), and they said they would try to do that, but they pressed me on one point: why?

Why did this suddenly stop “working”? Well, apparently what stopped working was that the XML piece of the process would choke on the decimal being input. I do not have any more specific details, but let us just say that the simple thing that had changed since yesterday were the underlying numbers. Once that was agreed upon, they still wanted to know what to do, which meant I needed to answer for how MS SQL was doing the math.

I put together a quick test to see if I could reproduce the behavior. I ran the following:

DECLARE @first decimal(18,2), @second decimal(18,2)
SET @first = -81234756.87
SET @second = 97813894778.34
SELECT @first/@second

Which returns:
-0.00083050324347158804

Okay, so how do I go from two identical data types and get something different? A quick check of the BOL about division states:

Result Types
Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

Okey-dokey, what about precedence? In my mind, it does not apply since we are using identical data types, and I believe I am correct. Of course, that is not very helpful. Now I am going to be late getting home, but I am quite interested to know what the heck is happening.

I bounce through the BOL a bit more and eventually find this nugget (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fbc9ad2c-0d3b-4e98-8fdd-4d912328e40a.htm). Finally, I have an answer. Turns out, when doing division involving decimals, there is a specific formula used to calculate the precision and scale of the resultant data type. In this case, I believe I have taken two decimal(18,2) data type, performed division, and gotten back a decimal (38,21) in return. How lovely, and very unexpected.

So, my quick answer (you need to convert) turns out to the the right one at this time. Thankfully I have another satisfied customer. Of course I was late getting home, which made my wife late for some shopping. So, any gains made with my colleagues were more than offset by my losses to Nordstrom’s.