Virtual tally table function

Question

Virtual tally table function

Steven Willis

SSCrazy Eights

Points: 9893
More actions
July 22, 2013 at 11:03 pm

#280356

Comments posted to this topic are about the item Virtual tally table function

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 1

While developing this function several alternative versions were suggested and tested. Each version seems to have its pros and cons. The posted version limits the lower-bound to > 0. To view the alternative versions, testing methodology and test results see the attached file which has all the necessary scripts for anyone who wants to jump in and offer suggestions.

craig buckham SSC Enthusiast Points: 129 More actions · Answer 2

Here is a slightly altered version that will print results between (and including) any two integers, forward or backward. I also included the row number, since it is often useful.

create FUNCTION [dbo].[TallyFromTo]

(

@start BIGINT

,@stop BIGINT

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

/* Uses the Bates algorithm for generating the rows from */

/* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */

/*

Usage:

SELECT N FROM [dbo].[TallyFromTo](1001,1100)

SELECT N FROM [dbo].[TallyFromTo](-10,100)

SELECT N FROM [dbo].[TallyFromTo](0,1000)

SELECT N FROM [dbo].[TallyFromTo](3,-20)

SELECT N FROM [dbo].[TallyFromTo](-1,-1)

SELECT N FROM [dbo].[TallyFromTo](1001,1000)

SELECT N FROM [dbo].[TallyFromTo](1,1000)

*/

WITH parms as (select SIGN(@stop-@start) as "sgn"),

T1(F) AS

(

SELECT 1 UNION ALL

SELECT 1

),

T2(F) AS

(SELECT 1 FROM T1 A, T1 B),

T3(F) AS

(SELECT 1 FROM T2 A, T2 B),

T4(F) AS

(SELECT 1 FROM T3 A, T3 B),

cteTally(RN) AS

(

SELECT TOP (ABS(@stop-@start)+1)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM T4

)

SELECT

RN as "RowN", p.sgn*RN+@start-p.sgn as "N"

FROM

cteTally T cross join parms p

;

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 3

Thanks for the feedback!

I haven't had a chance to try your version yet, but I'll run it through my test script when I get time and will post my results.

This will be heresy to some, but sometimes getting to a specific unique goal (such as including negative numbers or adding an additional column) at the cost of some performance may be necessary!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Steven - Hey dude, thanks for the credit even though I'm sure my contribution was minimal.

Didn't notice it on publication because I was watching for an article and besides I've just returned from an extended visit to Papua New Guinea where my favorite SQL application just went live.

Tally tables rock!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 5

Speaking of rocks, I feel like a rock because I totally missed this script entry. Thanks for the kudos, Steven, but I can't claim this method as my original thought. Itzik Ben-Gan was the first person I know of that came up with this and I've just been the one to push the idea of Tally Tables/CTEs to have them become household words.

It's really good to see that so many people have taken up the banner and are creating wonderful permutations of their own.

Shifting gears, it's also wonderful to see people doing "million row testing" to come up with actual performance data instead of merely speculating what works better based on code content or "in my experience" claims like it used to be in the early days. Well done!

And, one more time...

/* Uses the Bates algorithm for generating the rows from */ /* http://www.sqlservercentral.com/scripts/Tally+Table/99617/ */

Although I certainly applaud Mr. Bates for his efforts, Itzik published the TOP optimization years before Mr. Bates did. I'm not sure I can quickly find the article that Itzik published but I'll look for it. There's also an optimization I came up with for starting such a thing at "0" instead of subtracting 1 from each generated value. I'll try to remember to post that after work, as well.

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

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 6

Thanks Jeff. Any improvements (performance tested of course!) will be integrated and all ideas are welcome.

Concerning credits...I'll go ahead and make sure the proper credit is applied. There's plenty to go around and I just want people to know I'm just a "tool builder" and not the originator of the base methods.

As far as comment on the Virtual Tally Table function itself: there seems to be more variations of input desired than the base function offers. The range of numbers, for example, could be base 0 or 1, or could allow negative numbers, or have an enforced upper-limit, etc. I think I'll add some additional parameters, but will have to see how that affects performance. I look forward to any ideas for extending input options.

craig buckham SSC Enthusiast Points: 129 More actions · Answer 7

The TallyFromTo listing posted above on 7/25/2013 does allow for negative numbers, and also allows a choice of direction. I did not check the timing, but I guarantee it is fast enough to be used any time a tally function is needed.

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 8

I'll make a list of possible changes for review so we can all contribute and not have to do stuff over and over. Thanks.

1 Negative numbers

2 Base 0 or 1

3 Number direction

4 ?

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Jeff Moden (8/15/2013)
Speaking of rocks, I feel like a rock because I totally missed this script entry. Thanks for the kudos, Steven, but I can't claim this method as my original thought. Itzik Ben-Gan was the first person I know of that came up with this and I've just been the one to push the idea of Tally Tables/CTEs to have them become household words.

An idea is but an opportunity without promotion. Good marketing gets those ideas out to the world and leads to adoption.

Look at Ginsu Knives. Without Ronco, it would have been a mere blip on the historical radar, but I'm sure everyone knows what it is, and yes I have a set I bought more than 30 years ago.

I consider you the chief-marketeer, or more appropriately most visible proponent of the tally table as a tool. Inventor and proponents each have their place in history. The question is whose contribution will be better remembered when the history books are written.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St