October 23, 2012 at 9:06 pm
Comments posted to this topic are about the item Five percent rounded down
October 23, 2012 at 10:58 pm
god start with a coffee +2 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 23, 2012 at 11:54 pm
Very interesting, thank you for the question.
(and Happy Dasara to all)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
October 24, 2012 at 12:18 am
Nice question.
The trick of adding 0.000000001 to the multiplicand might work sometimes, but it isn't the best way to handle this.
Until someone introduces proper modern floating point into SQL (which would eliminate the problem completely) the way to handle this is to use round, which means going via numeric. So in this case, change the calculation for Quota3 to be either
select @Quota3 = round(@Value*cast(1.05 as numeric(5,2)) ,0);
or
select @Quota3 = round(cast((cast(@Value as float) * 1.05) as numeric(38,27)),0);
and the problem goes away. (and of course there should be similar changes in the calculations of the other two numbers).
Note that it is necessary, in the second version, to change from using real to using float if you want it to work whatever your integer is. Real (float(24)) is not a sensible type to use for this, since it can't represent all 32 bit integers; so it makes sense to change real to float, which can, as well as using round.
But proper modern floating point would be much much better. It's about time we were hearing something about it for SQL Standards, as it's now 50 months since IEEE 754-2008 was published.
Tom
October 24, 2012 at 12:18 am
Thanks for the question, it is a great point to learn from! I hadn't seen this before, but know there are always issues with real and float numbers so had to check, and the title kind of gave me a clue that something wouldn't be right.
I guess you only really know this if it has bitten you already!
October 24, 2012 at 12:28 am
Interesting question, thanks.
I do wonder how people are supposed to get this right without running the script.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 24, 2012 at 12:59 am
Lets play a bit:
Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);
-- Result is 105, 104
The result is very strange for me. Does SQL Server the calculation from inside to outside or not? :w00t:
October 24, 2012 at 2:07 am
palotaiarpad (10/24/2012)
Lets play a bit:
Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);-- Result is 105, 104
The result is very strange for me. Does SQL Server the calculation from inside to outside or not? :w00t:
...change the second "real" to "numeric" it gives same as 105...
(as it says in BOL "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. ")
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
October 24, 2012 at 2:33 am
Nice question.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
October 24, 2012 at 2:33 am
My problem is, that the first expression evaluates to 105, and if i cast the 105 to int it should remain 105 and not less.
October 24, 2012 at 2:51 am
This was removed by the editor as SPAM
October 24, 2012 at 3:02 am
Why would you define your variables as int if you are going to do non-integer calculations with them? Change the declarations to
declare @Quota1 real, @Quota2 real, @Quota3 real, @Value real;
and the issue disappears.
If you want to round/truncate the results, then do this explicitly, rather than relying on implicit convcersions,
October 24, 2012 at 3:51 am
The question is not bad (though I think a bit pointless; I hope people will never use this kind of data type mixing and complex ways to do simple calculations in real code). But the explanation is incorrect. It keeps mentioning reals, even where they are not involved. And some of the responses suffer from similar problems.
SO, let's look at this one step at a time:
select @Quota1 = @Value * 1.05;
Here, @Value is integer, and 1.05 is a constant. SQL Server will type this constant as decimal (not float!!) with the minimum required precision and length: decimal(3,2).
Since SQL Server can't multiply mixed data type, it uses the rules of data type precedence (http://msdn.microsoft.com/en-us/library/ms190309.aspx) to determine the required implicit conversions, and it will convert the integer value to decimal. To decimal(10,0) to be precise (because the largest int value has 10 digits).
Then, the multiplication is carried out. Rules for precision, scale, and length of operations (http://msdn.microsoft.com/en-us/library/ms190476.aspx) are applied, so that the result is now decimal(14,2), with a value of 21.00.
For assigning this to the integer @Quota1, it is truncated to 21.
select @Quota2 = cast(@Value as real) * 1.05;
The CAST is done first, so that the first operand (the integer value 20) is converted to real. 1.05 is still decimal(3,2), but now rules of data type precedence say that 1.05 will be converted to real. The calculation is done, the result is then truncated to integer. Since real uses approximate numbers, the result before truncation can be anything between 21 minus tiny fraction to 21 plus a tiny fraction - so after truncating, it is either 20 or 21. In this case, the value just happens to be 21.0000000000whatever, so we are lucky and get 21.
This ambiguity could have easily been avoided by using the ROUND function - that will always return the expected value.
select @Quota2 = ROUND(cast(@Value as real) * 1.05, 0);
Set @Value = 100;
select @Quota3 = cast(@Value as real) * 1.05;
Same as before - 100 is converted to float, 1.05 is converted to float, they are multiplied, and the result is somewhere very near to 105, which is then truncated to int. And in this case, the internal float results just happens to be 104.999999999whatever, so it's truncated to 104.
Again, rounding would have avoided the issue.
I guess the bottom line of this QotD is that if you mess up your code by mixing dat types and not handling the conversions appropriately, you'll get messes up results.
Tom:
the way to handle this is to use round, which means going via numeric.
Correction - you don't have to go via numeric; ROUND will gladly accept float data as its input.
palotaiarpad (10/24/2012)
Lets play a bit:
Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);-- Result is 105, 104
The result is very strange for me. Does SQL Server the calculation from inside to outside or not? :w00t:
(...)
My problem is, that the first expression evaluates to 105, and if i cast the 105 to int it should remain 105 and not less.
Both functions first calculate a real result. That result is not exactly 105, but terribly close. The first one stops there (so it is returned as real to SSMS, which then does some rounding before displaying it - that's why you see 105, instead of 104.999999999whatever), and the second one goes on to cast as integer, which truncates, then sends that integer value to SSMS.
October 24, 2012 at 4:23 am
Nice Question.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply