FizzBuzz

  • Gail Shaw

    SSC Guru

    Points: 1004424

    chillw1nston (1/28/2014)


    not sure what your getting at. I guess because it runs recursively and relys on previous result set to generate next which is a more programmatical construct?

    Yup.

    Would you advocate a cross join approach using system tables instead? Thats all i can think of without looping

    Cross join with system tables or cross join with row constructors (SQL 2008+)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • chillw1nston

    SSC Enthusiast

    Points: 147

    Thanks!

  • Gail Shaw

    SSC Guru

    Points: 1004424

    I’m not saying that recursive CTEs are bad, wrong, terrible, never use. Just saying be aware of how they behave.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 993402

    GilaMonster (1/28/2014)


    chillw1nston (1/28/2014)


    --Now scalable, set based and added a fizzbuzz!

    DECLARE @threshold int = 100

    ;WITH numbers ( seqnum )

    AS ( SELECT 1 AS seqnum

    UNION ALL

    SELECT seqnum + 1 AS seqnum

    FROM numbers

    WHERE seqnum < @threshold

    )

    Recursive CTEs are questionably set-based (will leave it to you to figure out why) and slower than many other methods of row generation.

    I agree with Gail. Recursive CTEs that “count” or process individual rows are a form of “Hidden RBAR” that can be much worse than a well written While Loop. Of course, for the production of regular numeric sequences, both should be avoided. Please see the following article for more information and performance comparisions of 3 other methods that absolutely blow Recursive CTE number generators out of the water even for relatively small numbers of rows.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    As Gail also suggested, Recursive CTEs do have some good uses and shouldn’t be avoided “just because”. As with all else in SQL Server, “It Depends”.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 4 posts - 361 through 364 (of 364 total)

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