October 21, 2004 at 7:05 am
Can anyone explain to me the behaviour of these 3 bits of SQL, they are all the same, but the precision and scale of the variables have been changed.
(1)
DECLARE @Spam decimal (28,12)
DECLARE @Chips decimal (28,12)
SET @Chips = 1.22323235
SET @Spam = 2.22222225
PRINT ( ( ( 1 + (@Spam / 100) )*(1 + (@Chips /100) ) ) - 1) * 100
(2)
DECLARE @Beans decimal (28,16)
DECLARE @ExtraSpam decimal (28,16)
SET @Beans = 1.22323235
SET @ExtraSpam = 2.22222225
PRINT ( ( ( 1 + (@ExtraSpam / 100) )*(1 + (@Beans /100) ) ) - 1) * 100
(3)
DECLARE @Egg decimal (24,16)
DECLARE @LobsterThermidor decimal (24,16)
SET @Egg = 1.22323235
SET @LobsterThermidor = 2.22222225
PRINT ( ( ( 1 + (@LobsterThermidor / 100) )*(1 + (@Egg /100) ) ) - 1) * 100
The results I get are:
(1) 3.472600
(2) 3.4726375
(3) 3.472637541450898
Why am I only getting 4 decimal places of accuracy in example (1) when the scale is set to 8?
Why am I only getting 8 decimal places in example (2) and 16 in example (3) when both have a scale of 16?
Any comments or ideas appreciated.
Thanks in advance.
October 21, 2004 at 9:49 am
The arithmatic is adjusting the precision and scale in an automated attempt to retain a data container of sufficient accuracy. Since you have such a high precision value you immediately begin to exceed the maximum of 38 (28 for earlier versions of SQL Server). From BOL look up "scale of data type". The following is copy/pasted from there. The * at the bottom after the grid is of key importance for you.
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.
| Operation | Result precision | Result scale * |
|---|---|---|
| e1 + e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 - e2 | max(s1, s2) + max(p1-s1, p2-s2) | max(s1, s2) |
| e1 * e2 | p1 + p2 + 1 | s1 + s2 |
| e1 / e2 | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
* 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.
October 22, 2004 at 3:27 am
Thanks. I assumed that SQL was clever enough to ignore any unused portion of the precision when doing the above sums. As the integer portion of my numbers was small, I had assumed it would work. Silly me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply