SET ROWCOUNT and table variable

  • Comments posted to this topic are about the item SET ROWCOUNT and table variable

  • This is an excellent question, thank you. I answered it correctly because for years I cannot find the answer to the following question: why would anyone ever consider casually using float data type? I understand that there might be some specific scenarios where the inaccuracy of calculations are not important and the range of the numbers is huge, but using float for no good reason is astonishingly bad not to say plain silly. The query in Qotd is one fine example demonstrating this point.

    Oleg

  • Thanks for the question, I had to puzzle it out for a while, but got it right.

  • for testing folating point the same can be written as

    DECLARE @i float,@rc int

    set @i = 0

    while @i <> 1

    begin

    set @i = @i + 0.1

    end

    select @i

    then the loop never executes.

    I observer some intrested behavior of floating point

    DECLARE @i float,@rc int

    set @i = 0

    while @i <> 1

    begin

    set @i = 0.8999 + 0.1

    end

    select @i

    the above will never executes. we use 0.1 instead of 0.8999 other wise we can use 0.2,0.3,0.4,0.5,0.6,0.7,0.8.but when i added 0.9 it executes

    and show 1 as answer.

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

  • The answer was almost obvious from the "do not run it on production server" recommendation.

    But, a good question for highlighting the pitfalls of "float".


    Cheers,
    - Mark

  • This was removed by the editor as SPAM

  • Thanks for a great question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Great question. I got it wrong though. I do not really understand how the scope of the type variable works. It seems you can define it over and over again, without an error being thrown?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Very interesting question and great explanations. Thanks

  • isn't it possible to use approximation if one is not specific of the exact value of the approximation, like "less than" and "greater than" ?

  • It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).

    When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.

  • da-zero (8/26/2010)


    I do not really understand how the scope of the type variable works. It seems you can define it over and over again, without an error being thrown?

    and

    martin.whitton (8/26/2010)


    It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).

    When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.

    The variable is actually declared once. Declarations are not relly executable statements. They are processed when a batch is parsed, not when it it executed. The declaration has to be positioned before the use in "left to right / top to bottom" order, not in execution order. That is why this works, even though the code path that containst the declaration is never executed.

    IF 1 = 2

    BEGIN;

    PRINT 'Skip this';

    DECLARE @a int;

    END;

    ELSE

    BEGIN;

    SET @a = 1;

    END;

    SELECT @a;

    And this works as well, even though the part where the declaration sits is executed AFTER the assignment:

    GOTO Label2;

    Label1:

    DECLARE @b-2 int;

    SELECT @b-2;

    GOTO Label3;

    Label2:

    SET @b-2 = 3;

    GOTO Label1;

    Label3:

    go

    (Note that I do not endorse using GOTO in T-SQL code, nor deliberately writing spaghetti code for any other purposes than illustrating the difference between order of parsing and order of execution)


    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/

  • stewartc-708166 (8/25/2010)


    The only possible use for the float datatype that I have found is in the calculations used in astro-physics, where the distance between stars and galaxies is an approximation, at best

    Add physics, chemistry and all other parts of science that deal with measured data. All measurements are always approximations.

    When you have to deal with extremely large values (as in astro-physics) or extremely small values (like atomic weight or distance in chemistry), floating point data is the only reasonable choice. For other data, both floating point and fixed point canbe used though floating point often is better).


    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/

  • Last comment, and then I'll shut up (for now)

    Open Minded (8/26/2010)


    isn't it possible to use approximation if one is not specific of the exact value of the approximation, like "less than" and "greater than" ?

    Yes. A test of "IF @float > 1.0" is okay.

    And if you really have to test for equality with floating point data, you have to consider how many of the decimals are relevant and then round: "IF ROUND(@float, 3) = 1.000" should work. If you change the WHILE in this question to "while round(@1,3) <> 1", the code finishes and returns a single value.


    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/

  • Floats can be useful when the same column needs to be able to hold either large values (lots of numbers before the decimal point) or very small values (lots of numbers after it).

    There are of course loads of pitfalls to be aware of, but in general, so longer as you always round the number to an appropriate number of decimal places then things will work.

    eg in this qotd

    while @i <> 1

    could be something like

    while round(@i,8) <> 1

    However, they are definitely better avoided if there's a reasonable alternative!

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

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