Home Forums Article Discussions Article Discussions by Author Discuss content posted by Adam Aspin Using a CTE as a Tally Table RE: Using a CTE as a Tally Table

  • August 9, 2018 at 8:22 am

    #2001027

    BluePeter - Thursday, August 9, 2018 5:07 AM

    Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AM

    Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
    😎

    Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
    But no difference for 52 (or 53)
    I'll keep yours in mind, never seen it before.

    The reason you think there is no difference is because of what you're measuring and the way your measuring it.  There's another thing to consider, as well...

    If you practice the wrong way just because of low row counts, you'll never get good at the right way.  You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers.  You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.

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

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)