Here are the SQL2K SP3 results:
..002156
..0021557
..002155680
Looks the same as yours ...
Gert-Jan Strik wrote:
Looks like a bug to me... I ran the script below on SQL 7.0 (I know, that's not SQL2000, maybe someone could try it there), and look at the surprising results... It started complaining exceeding the maximum precision when I raised the precision to 29. However, it does not seem to work correctly starting at precision 23. declare b2 decimal(24,8) declare NonNestingPercent decimal(24,8) set b2 = 0.017964 set NonNestingPercent = 0.12 select b2 * NonNestingPercent go declare b2 decimal(23,8) declare NonNestingPercent decimal(23,8) set b2 = 0.017964 set NonNestingPercent = 0.12 select b2 * NonNestingPercent go declare b2 decimal(22,8) declare NonNestingPercent ...
Looks like a bug to me...
I ran the script below on SQL 7.0 (I know, that's not SQL2000, maybe
someone could try it there), and look at the surprising results... It
started complaining exceeding the maximum precision when I raised the
precision to 29. However, it does not seem to work correctly starting at
precision 23.
declare b2 decimal(24,8)
declare NonNestingPercent decimal(24,8)
set b2 = 0.017964
set NonNestingPercent = 0.12
select b2 * NonNestingPercent
go
declare b2 decimal(23,8)
declare NonNestingPercent decimal(23,8)
set b2 = 0.017964
set NonNestingPercent = 0.12
select b2 * NonNestingPercent
go
declare b2 decimal(22,8)
declare NonNestingPercent decimal(22,8)
set b2 = 0.017964
set NonNestingPercent = 0.12
select b2 * NonNestingPercent
go
----------------------------------------
..002156
(1 row(s) affected)
----------------------------------------
..0021557
(1 row(s) affected)
----------------------------------------
..002155680
(1 row(s) affected)
Gert-Jan
Here are the SQL2K SP3 results:
..002156
..0021557
..002155680
Looks the same as yours ...
Gert-Jan Strik wrote:
Hmm,
May be intermidiate calculation problems...
I tried declaring the variables as decimal(24,10) then casting after the
final result, and I got the following.
--------------------------
..00215568
(1 row(s) affected)
--------------------------
..00215568
(1 row(s) affected)
--------------------------
..00215568
(1 row(s) affected)
Here's what I ran...
declare b2 decimal(24,10)
declare NonNestingPercent decimal(24,10)
set b2 = 0.017964
set NonNestingPercent = 0.12
select CAST(b2 * NonNestingPercent AS decimal(24,8))
go
declare b2 decimal(23,10)
declare NonNestingPercent decimal(23,10)
set b2 = 0.017964
set NonNestingPercent = 0.12
select CAST(b2 * NonNestingPercent AS decimal(24,8))
go
declare b2 decimal(22,10)
declare NonNestingPercent decimal(22,10)
set b2 = 0.017964
set NonNestingPercent = 0.12
select CAST(b2 * NonNestingPercent AS decimal(24,8))
go
Dan C Douglas wrote:
Thanks for the help..
Strangly enough I changed the precision from 24 to 23 and I was able to get
it to one additional decimal place, and changing it to 22 gave me 8
decimals..
I don't understand though as you saw in my results the one number that also
had a 24 precision and 8 scale had well over 10 or 12 significant digits...
Especially since:
The default maximum precision of numeric and decimal data types is 38. In
previous versions of SQL Server, the default maximum was 28.
I am nowhere near the 38, nor the 28 max precision...
"Gert-Jan Strik" <nl> wrote in message
news:nl...
The reason for this behavior is that a numeric value can never have more
than 38 digits.
When you are multiplying two numeric(24,8) values, the database must select
an appropriate temporary format to hold the result. To avoid possible
overflow, the database always allocates as many digits as necessary to the
left of the decimal point first.
The selected format in this case is numeric(32,6)
32 is the maximum number of digits to the left of the deicmal point after
the multiplication (24-8)*2
6 is the maximum number of decimals that can fit (38-32)
Thus, when multiplying two numeric(24,8) numbers, you get the result as a
numeric(32,6)
To correct your problem you could use this:
Cast((Cast(BallparkNumberOfBins as float) * Cast(NonNestingPercent as
float)) as Decimal(24,8))
This will perform the multiplication with full double-precision floating
point precision, and then round the result to 8 decimals
/SG
That makes sense... I assume you meant to say that numeric(24,8) *
numeric(24,8) results in numeric(38,6) (instead of numeric(32,6)).
In that case, it would also be a solution to cast to lower precision
before multiplying. Of course, this requires the values to fit in 19
digits.
For example:
Cast(BallparkNumberOfBins as numeric(19,8)) * Cast(NonNestingPercent as
numeric(19,8))
This would result in numeric(38,16)
Gert-Jan
Stefan Gustafsson wrote:
Bookmarks