Five percent rounded down

  • Comments posted to this topic are about the item Five percent rounded down

  • god start with a coffee +2 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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.

  • 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

  • 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!

  • 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

  • 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:

  • 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.

  • Nice question.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • 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.

  • This was removed by the editor as SPAM

  • 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,

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nice Question.

  • Any chance we could get Hugo to rewrite BOL, so they are correct and useful? Thanks for the, as usual, great explanation.

    Edited to correct punctuation.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply