x

Decimal calculations in SQL 2000 vs SQL 2005

This is a follow on from this question about handling large decimals in SQL 2000. I'm not looking for solutions - what I want to know is WHY it behaves in this way.

So to recap, the original question was how to perform this calculation in SQL 2000

select 100333601600192534100000300182947.0 / 97

as this results in

Arithmetic overflow error converting expression to data type numeric 

Fatherjack came up with a solution using a lot of custom functions, and Oleg suggested a trick whereby he moved the decimal point around.

The calculation works fine in SQL 2005, producing the result

1034367026806108598969075259618.010309

Some background... the decimal datatype can store a maximum of 38 digits, and is defined as decimal(precision,scale) where precision is the total number of digits, and scale is the number of digits to the right of the decimal point. Decimal datatype is limited by the rule (SQL 2000 | SQL 2005)

0 <= s <= p <= 38

Looking at the values in our calculation, we have

value                                  precision     scale
100333601600192534100000300182947.0      34            1
97                                       10            0
1034367026806108598969075259618.010309   37            6

all of which fit the rules!

However there are rules (SQL 2000 | SQL 2005) about the resulting datatype when decimal data types are used so if we take

e1 = 100333601600192534100000300182947.0
e2 = 97

and use the rule

e1/e2 : result precsion = p1 - s1 + s2 + max(6, s1 + p2 + 1)
result scale = max(6, s1 + p2 + 1)

we get

precision = 33 - 0 + 0 + max(6, 0 + 10 + 1)
= 33 + max(6, 11)
= 33 + 11
= 44

scale = max(6, 0 + 10 + 1)
= max(6,11)
= 11

oh no, we have a precision of 44 and a scale of 11 - this breaks the rules

Ahah! BUT!

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated. (BOL)

suggests that the result will be forced into a decimal(38,5)

So now the questions:

  • why does this work in 2000 and not 2005, especially when the BOL pages between SQL 2000

and SQL 2005 contain the same info

  • what changed between the 2 versions, and where can I find the info!!!

  • if the result is forced into a decimal(38,5), why on SQL 2005 the result has a scale of 6

It feels like it is in the intermediate steps of the calculation where SQL 2005 will allow the precision to blow, as the final result is ok, but SQL 2000 has problems.

Any ideas?

more ▼

asked Apr 29 '10 at 08:05 AM in Default

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

Haven't a clue, but I'm contacting someone who might.
Apr 29 '10 at 09:57 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

SQL Server 2005 never reduces the scale to less than 6 when it encounters a result that requires precision > 38. You might notice that the 'magic number' there (6) is the same as in the documented max expressions.

There have been so many little bugs in this area, it's hard to remember whether this particular issue was fixed in 2005 RTM or later...I can't find the reference for the moment.

The explicit minimum of 6 doesn't appear to be properly documented either, though it is alluded to in many places, including this KB article

Paul

more ▼

answered Apr 29 '10 at 11:13 AM

SQL Kiwi gravatar image

SQL Kiwi
1.1k 1 4

+1 : Cool that explains the (38,6) - any ideas on the rest?
Apr 29 '10 at 11:36 AM Kev Riley ♦♦
Unsure what else to add. Behaviour changes due to bug fixes.
Apr 29 '10 at 12:07 PM SQL Kiwi

Kev,

I thought that you and I have figured it out at least to some degree. The only problem with 33 digits input is that because the scale wants to stay at 6, the 2000 engine does not see that it is actually possible that the number of significant digits can be less after division, and therefore it should be safe to apply the documented rule to reduce the scale accordingly. This is why we were able to shift 33 to the left leaving 32 integral part digits and 32 + 6 is no longer greater than 38, so it works. In the same fashion we would have to shift the 34 digits input 2 to the left etc.
Apr 29 '10 at 01:12 PM Oleg
Oleg: just trying to figure out why, in the face of unchanged documentation, it acts differently, thats all.
Apr 29 '10 at 02:57 PM Kev Riley ♦♦
Paul : so we think this is just down to bug fixes - would be good to find that reference
Apr 29 '10 at 03:51 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x472
x35
x5

asked: Apr 29 '10 at 08:05 AM

Seen: 2555 times

Last Updated: Apr 29 '10 at 12:48 PM