August 4, 2019 at 8:15 pm
Comments posted to this topic are about the item Create a Tally Function (fnTally)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2019 at 4:49 pm
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.
August 7, 2019 at 5:41 pm
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
Change is inevitable... Change for the better is not.
August 7, 2019 at 7:07 pm
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
Totally agreed on that, Jason! It'll be interesting to see if anyone posts theirs. And, thank you for posting yours!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2019 at 4:21 pm
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
Change is inevitable... Change for the better is not.
August 13, 2019 at 5:40 pm
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: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 13, 2019 at 8:14 pm
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.
August 13, 2019 at 8:18 pm
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
Change is inevitable... Change for the better is not.
August 13, 2019 at 8:27 pm
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
Change is inevitable... Change for the better is not.
August 13, 2019 at 9:20 pm
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: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
August 14, 2019 at 12:03 am
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
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".
August 14, 2019 at 1:48 am
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
Change is inevitable... Change for the better is not.
August 14, 2019 at 1:49 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply