Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2005-01-28 : 14:11:27
|
Ok, take this simply script and execute in QA:DECLARE @v1 DEC(19,10), @v2 DEC (19,10), @v3 DEC(19,10)SET @v1 = 7.85SET @v2 = 455SET @v3 = .000001SELECT 7.85 * 455 * .000001,@v1 * @v2 * @v3 Works great, as you can see. However, change all the DEC definitions to (19,9) and you will see that the result is rounded to the 6th decimal place. Why? I thought scale defined how many digits to the right of the decimal. The final answer requires only 8 decimal places, so why does it round when the vars are declared with a scale of 9?Using SQL Server 2000 sp3.Thanks.3P==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-01-28 : 15:07:27
|
I think the what you're seeing highlights the difficulty with performing arithmetic operations on numeric/decimal datatypes. SqlServer has to define result types based purely on the types (scale and precision) of the operands rather than their values.Notice that if you simplifiy (slightly) your above statment by casting an intermidiate result to your defined scale and precision you get the correct results. ie:SELECT convert(dec(19,9),@v1 * @v2) * @v3Here is an excerpt from BOL (topic: Precision, Scale, and Length)quote:
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.
I know none of this answers your question but when you get as confused as I am, you just won't care anymore...Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Decimal multiplication driving me crazy!