Post reply

Create a Tally Function (fnTally)

  • August 4, 2019 at 8:15 pm

    #3669281

    Comments posted to this topic are about the item Create a Tally Function (fnTally)

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

  • August 7, 2019 at 4:49 pm

    #3669573

    Jeff,

    Kudos on another excellent article. It's always interesting to see your take on these types of functions.

    For comparison, here is an altered version of a similar function I created a couple years back (I changed it from the older base 10 to match your newer base 256).

    DECLARE 
    @NumOfRows int = 100000,
    @StartWith bigint = 10;
    WITH
    cte_n1 (n) AS (SELECT 1 FROM ( VALUES--256 initial rows
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ) n (n)
    ),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),-- 65,536 rows
    cte_tally (n) AS (
    SELECT TOP (@NumOfRows)
    @StartWith + (ROW_NUMBER() OVER (ORDER BY a.n) - 1)
    FROM
    cte_n2 a CROSS JOIN cte_n2 b-- 4,294,967,296 rows
    )
    SELECT 
    t.n
    FROM
    cte_tally t;
    GO

    Note that it is slightly different in that it has you set the total number of rows rather that the the MaxN value and It allows you to choose any BIGINT (positive or negative) as the starting point, rather than just 1 or 0.

    • This reply was modified 5 years, 3 months ago by  Jason A. Long.
    • This reply was modified 5 years, 3 months ago by  Jason A. Long.

  • August 7, 2019 at 5:41 pm

    #3669600

    Thanks for the very kind feedback, Jason.

    Interesting code, Jason.  While I've seen such a thing before, I've not seen many folks that provide a starting value and number of rows to return.

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

  • August 7, 2019 at 7:07 pm

    #3669643

    Thanks Jeff,

    I don't know that one version has an inherent advantage over the other. I just think it's interesting to see alternate solutions to problems.

  • August 7, 2019 at 9:59 pm

    #3669676

    Totally agreed on that, Jason!  It'll be interesting to see if anyone posts theirs.  And, thank you for posting yours!

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

  • August 13, 2019 at 4:21 pm

    #3671093

    Heh... having this posted as a script has already started to pay off for me not only in a couple of posts on this forum but also on the "Azure Feedback" site.  Erland Sommarskog requested that MS build in a machine language level "Tally Function" over 11 years ago.  It's still active but MS has taken no other action.  People keep posting alternatives and someone just posted an alternative that uses an rCTE (which can usually be beat by a well written WHILE loop).  It took over 9 seconds to generate and dump a million (out of order) values to a "Bit Bucket" variable.

    I posted a bit of code that did the same thing using this function and it took less than a milli_second on my 8 year old laptop running dual i5's threaded to 4.

    For anyone interested in MS building such a function into T-SQL, please see the following thread.  It used to have 278 votes until MS moved the "Connect" system to "Azure Feedback".  The old comments are now part of the description and the new ones occur well below that.  Please vote!

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-of-numbers?tracking_code=d3ee165fa0ebda375d3fe5e1c6eeaf0d

     

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

  • August 13, 2019 at 5:40 pm

    #3671117

    Hi Jeff! Here's a version that I've used for a while that's been pretty handy for me:

    CREATE FUNCTION dbo.fnTally(@RowCnt bigint, @ZeroOrOne bit = 1)
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
    ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
    ,e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    ,e8(n) AS (SELECT 1 FROM e4 a CROSS JOIN e4 b) -- 16^8 or 4,294,967,296 records (65,536*65,536)
    SELECT 0 AS n WHERE @ZeroOrOne = 0
    UNION ALL
    SELECT TOP (@RowCnt-CAST(@ZeroOrOne as bigint)) ROW_NUMBER() OVER (ORDER BY n) AS n
    FROM e8;
    GO

    For those few cases that require an offset, I use the following:

    CREATE FUNCTION dbo.fnTallyOffset(@RowCnt bigint, @StartVal bigint = 1)
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
    ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
    ,e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    ,e8(n) AS (SELECT 1 FROM e4 a CROSS JOIN e4 b) -- 16^8 or 4,294,967,296 records (65,536*65,536)
    ,en(n) AS (SELECT TOP (@RowCnt+@StartVal-1) ROW_NUMBER() OVER (ORDER BY n) AS n FROM e8)
    SELECTn
    FROMen
    WHEREn >= @StartVal;
    GO

    LinkedIn: https://www.linkedin.com/in/sqlrv
    Website: https://www.sqlrv.com

  • August 13, 2019 at 8:14 pm

    #3671154

    Jeff Moden wrote:

    For anyone interested in MS building such a function into T-SQL, please see the following thread.  It used to have 278 votes until MS moved the "Connect" system to "Azure Feedback".  The old comments are now part of the description and the new ones occur well below that.  Please vote!

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-of-numbers?tracking_code=d3ee165fa0ebda375d3fe5e1c6eeaf0d

    Thanks for this Jeff, I has upvoted this on the old connect site but apparently, mine was one of the votes that didn't migrate. Added my upvote to the new site.

  • August 13, 2019 at 8:18 pm

    #3671156

    Jason A. Long wrote:

    Jeff Moden wrote:

    For anyone interested in MS building such a function into T-SQL, please see the following thread.  It used to have 278 votes until MS moved the "Connect" system to "Azure Feedback".  The old comments are now part of the description and the new ones occur well below that.  Please vote!

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-of-numbers?tracking_code=d3ee165fa0ebda375d3fe5e1c6eeaf0d

    Thanks for this Jeff, I has upvoted this on the old connect site but apparently, mine was one of the votes that didn't migrate. Added my upvote to the new site.

    Thanks, Jason.  Let's hope that it doesn't continue to fall on the deaf ears of MS.

     

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

  • August 13, 2019 at 8:27 pm

    #3671158

    Thanks fr posting your code, Aaron.  Good to see someone else using the "Union All" trick to get the zero value (it's also what I use) but I still have a hard time believing how much it costs in the function.  I wish MS had made it so that ROW_NUMBER() had a "start at zero" option.  Better yet, I wish MS would finally pony up an intrinsic function to replace the tricks we have to do to get a simple sequence of numbers to render out with some good performance.

    As for starting values, I've taken to always doing that external to the function simply because, if the calculations are done inside the function, it penalizes the stuff that truly only needs to start with 0 or 1.  For me and like I said in the article, the 0/1 thing is what I need about 99.9% of the time.  That being said, I'm equally amazed at the number of people that think rCTEs are the way to go for this type of thing.

     

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

  • August 13, 2019 at 9:20 pm

    #3671169

    No worries, Jeff. I also use the first one most primarily (99+%) with only a few use cases for the offset one. You got me to thinking about the UNION ALL issue. I wonder if this version would perform any better?

    CREATE FUNCTION dbo.fnTally(@RowCnt bigint, @ZeroOrOne bit = 1)
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
    ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
    ,e4(n) AS (SELECT 1 FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    ,e8(n) AS (SELECT 1 FROM e4 a CROSS JOIN e4 b) -- 16^8 or 4,294,967,296 records (65,536*65,536)
    SELECT TOP (@RowCnt-CAST(@ZeroOrOne as bigint)) ROW_NUMBER() OVER (ORDER BY n) - (CAST(~@ZeroOrOne as bigint)) AS n
    FROM e8;
    GO

    I am also amazed at the volume of folks who overuse recursive CTEs. It almost seems that they go out of their way to use them!

    LinkedIn: https://www.linkedin.com/in/sqlrv
    Website: https://www.sqlrv.com

  • August 14, 2019 at 12:03 am

    #3671186

    Aaron N. Cutshall wrote:

    I am also amazed at the volume of folks who overuse recursive CTEs. It almost seems that they go out of their way to use them!

    They do go out of their way to use them. There's a bizarre psychological thing that comes from learning something shiny and new, especially if they consider it be "advanced"... I'd put the use of PIVOT and UNPIVOT in the same category.

  • August 14, 2019 at 12:09 am

    #3671189

    Jeff Moden wrote:

    Jason A. Long wrote:

    Jeff Moden wrote:

    For anyone interested in MS building such a function into T-SQL, please see the following thread.  It used to have 278 votes until MS moved the "Connect" system to "Azure Feedback".  The old comments are now part of the description and the new ones occur well below that.  Please vote!

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-of-numbers?tracking_code=d3ee165fa0ebda375d3fe5e1c6eeaf0d

    Thanks for this Jeff, I has upvoted this on the old connect site but apparently, mine was one of the votes that didn't migrate. Added my upvote to the new site.

    Thanks, Jason.  Let's hope that it doesn't continue to fall on the deaf ears of MS.

    The sad part is that the internal mechanism to make it work already exists. Just look at the execution plan for any of these functions... They'll all have one or more Constant Scan nodes with a description of, "Scan an internal table of constants.".

    All they would have to do is provide a function that directly accesses this fabled "internal table of constants".

  • August 14, 2019 at 1:48 am

    #3671199

    Jason A. Long wrote:

    Aaron N. Cutshall wrote:

    I am also amazed at the volume of folks who overuse recursive CTEs. It almost seems that they go out of their way to use them!

    They do go out of their way to use them. There's a bizarre psychological thing that comes from learning something shiny and new, especially if they consider it be "advanced"... I'd put the use of PIVOT and UNPIVOT in the same category.

    It DOES give you a grand appreciation for a lot of the people on SSC!

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

  • August 14, 2019 at 1:49 am

    #3671200

    Jason A. Long wrote:

    Jeff Moden wrote:

    Jason A. Long wrote:

    Jeff Moden wrote:

    For anyone interested in MS building such a function into T-SQL, please see the following thread.  It used to have 278 votes until MS moved the "Connect" system to "Azure Feedback".  The old comments are now part of the description and the new ones occur well below that.  Please vote!

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-of-numbers?tracking_code=d3ee165fa0ebda375d3fe5e1c6eeaf0d

    Thanks for this Jeff, I has upvoted this on the old connect site but apparently, mine was one of the votes that didn't migrate. Added my upvote to the new site.

    Thanks, Jason.  Let's hope that it doesn't continue to fall on the deaf ears of MS.

    The sad part is that the internal mechanism to make it work already exists. Just look at the execution plan for any of these functions... They'll all have one or more Constant Scan nodes with a description of, "Scan an internal table of constants.".

    All they would have to do is provide a function that directly accesses this fabled "internal table of constants".

    Heh... wonder if that table is actually spt_values?

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

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

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