Create a Tally Function (fnTally)
For those that don't know, a "Tally Table", which some refer to as a "Numbers Table", is simply a table containing a single, very well indexed column of sequential numbers from 0 or 1 to some maximum number for use as a "Row Source" (also referred to as a "Pseudo Cursor" {thank you, R.Barry Young, for the term}) . Such a table is used in many ways to replace certain types of While loops and other forms of RBAR1. Please see the article at the following link for more information on that subject.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
The only disadvantages of a Tally Table are that it's limited for the maximum number it contains and it can produce a shedload of logical reads. The logical reads aren't a bad thing here but they can be annoying to a DBA that uses logical reads to search for bad code because bad code frequently also produces a lot of logical reads.
As it says in the flower box header in the code below, Itzik Ben-gan came up with a way of cascading CTEs (I refer to them simply as "cCTEs") to very quickly produce a sequence of integers starting at "1". Although almost imperceptibly slower than using an Tally Table, Mr. Ben-gan's method has the extreme advantages of having very high upper limits and they produce zero logical reads.
Sidebar: Note that "cCTEs" should not be confused with the much slower and seriously resource intensive "rCTEs" (short for "Recursive CTEs"), which are frequently used to do the same thing as the cCTEs. Please see the following link for more information on the comparison and the Hidden RBAR2 produced by rCTEs that count.
Hidden RBAR: Counting with Recursive CTE's
Mr. Ben-Gan's fine method has been a mainstay since at least 2009 (the date of his article) for many of the denizens of SQLServerCentral.com (including myself) and there have been many versions of the method that have been created and posted as high performance iTVFs (Inline Table Valued Functions) and directly in code as a kind of "inline view". You can view Mr. Ben-Gan's original article at the following link.
Virtual Auxiliary Table of Numbers
The script below is the version that I've been using for most of my production and personal implementations. It's about the same as the functions many others have posted. The reasons I'm finally posting mine are as follows:
- I very recently added a performance enhancement thanks to some research I was doing for another post (details are in the Revision History of the code).
- I'm getting older and keep forgetting to attach the code in posts where I use it. and, so, I wanted a link on the site I use it on (SQLServerCentral.com) that I could include in my signature line on the posts.
If you want to know more details about this function, please read the comments in the header of the code below.
If you want an introduction to the concept of a Tally or Numbers Table, please see the article at the following link.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Sidebar: I don't normally use "Hungarian Notation" but I also have a Tally table in many of my databases and didn't want there to be a naming conflict. An added benefit is that people can now tell others that their code would have run faster if they had used an fnTally function without it actually being an HR violation anymore. 😀
Last but not least, I'm sometimes asked why I don't make a Tally function that allows other than a 0 or 1 starting value that's also order-insensitive that also allows both positive and negative values for either input where the order of output is created by the implicit input of the starting and ending values. The answer is, I actually have written such a function. Now, it's not a whole lot slower duration wise (returns 10 Million values in about 1500ms) but, 99.9% of the time, I only need something that starts at 0 or 1 and can't justify either a 36% or 50% degradation in performance for something that I'll not need to use. I've only used such a thing once in more than 2 decades and I've not see a requirement for such a thing in any of the posts I've worked on in that same amount of time.
Thanks for listening, folks.
--Jeff Moden
1RBAR is pronounced "ree-bar" (like the steel rods stuck in cement and not going anywhere fast) and is a Modenism for "Row By Agonizing Row". It is used to describe procedural looping and recursion methods that process one row at a time or many of the same rows for each row processed even if the code appears to be Set-Based. RBAR typically and unnecessarily uses large amounts of CPU, is resource intensive, and generally takes longer than its Set-Based counter parts.
2Hidden RBAR is RBAR that doesn't look like RBAR. An example of this is an rCTE (Recursive CTE) that counts incrementally. It could also be an accidental many-to-many Join or correlated sub-query based on an inequality (which is actually Set-Based code) as well as other database programming structures and methods.
Copyright © by Jeff Moden – 04 Aug 2019 – All rights reserved
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 10 Quadrillion.
Usage:
--===== Syntax example
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
@ZeroOrOne will internally conver to a 1 for any number other than 0 and a 0 for a 0.
@MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.
Please see the following notes for other important information
Notes:
1. This code works for SQL Server 2008 and up.
2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works.
https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
4. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 4,294,967,296. If a larger
number is used, the function will silently truncate after that max. If you actually need a sequence with that many
or more values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending sort is
required, use code similar to the following. Performance will decrease by about 27% but it's still very fast
especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is implicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12( mi:ss).
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 07 Sep 2013 - Jeff Moden
- Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment.
This will also make it much more difficult for someone to actually get silent truncation in the future.
Rev 04 - 04 Aug 2019 - Jeff Moden
- Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of
CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which
is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES
clause, this code is "only" compatible with SQLServer 2008 and above.
- Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they
changed the name of the company (twice, actually).
- Update the flower box notes with the other changes.
**********************************************************************************************************************/ (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
GO 
