# Create a Tally Function (fnTally)

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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;WITHcte_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 rowscte_tally (n) AS (SELECT TOP (@NumOfRows)@StartWith + (ROW_NUMBER() OVER (ORDER BY a.n) - 1)FROMcte_n2 a CROSS JOIN cte_n2 b-- 4,294,967,296 rows)SELECT t.nFROMcte_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 3 years, 4 months ago by  Jason A. Long.
• This reply was modified 3 years, 4 months ago by  Jason A. Long.
• 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".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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 SCHEMABINDINGAS RETURNWITH 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 = 0UNION ALLSELECT TOP (@RowCnt-CAST(@ZeroOrOne as bigint)) ROW_NUMBER() OVER (ORDER BY n) AS nFROM 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 SCHEMABINDINGAS RETURNWITH 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)SELECTnFROMenWHEREn >= @StartVal;GO`
• 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!

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.

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

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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 SCHEMABINDINGAS RETURNWITH 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 nFROM 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!

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

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

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

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

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

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