The Dynamic Tally or Numbers Table

Question

The Dynamic Tally or Numbers Table

Viewing 10 posts - 151 through 159 (of 159 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

TheSQLGuru (10/5/2009)
For me it was Hunt the Wumpus, hand-coded on my TRS-80 Model III circa 1979. I think there was another one I did, something that started with a 'Z' - Zork, Zon? BTW, that "computer" had 4K of RAM and CASSETTE TAPE DRIVE EXTERNAL STORAGE - and I was the baddest *** geek for a hundred miles (small-town NC at the time). Oh, did I mention that my parents forked over $999 for it?!?! Man have we come a damn long way!

Wow - Zork!!! That takes me back!

Oh and 4K RAM?! Luxury! The VIC had 3.5K...and any moment now someone is going to pop up who had a ZX-80 with its 1K RAM and 4K total ROM. Yep, the 80, not the 81. The one which had to blank the screen when it wasn't completely idle :w00t:

I used to love nothing more than adjusting the azimuth of my tape heads to try to get it it 'turbo load' the latest game.

Yes, we have come an awfully long way. Kids these days don't know they're born etc etc etc 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Johan Bijnens SSC Guru Points: 135266 More actions · Answer 2

Give me a moment to whipe out a teardrop of nostalgia :alien:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Peter Pirker Ten Centuries Points: 1079 More actions · Answer 3

Have tried to run it (copy + paste) but get errors.

What could I be doing wrong?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Peter Pirker (12/9/2009)
Have tried to run it (copy + paste) but get errors.
What could I be doing wrong?

You may need to do some format fixing. The code provided originally was okay, but it appears that in preping for publication that some of the spaces have been removed.

Peter Pirker Ten Centuries Points: 1079 More actions · Answer 5

Yes, I noticed - sorry to trouble you, and thank's for a great article.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Peter Pirker (12/9/2009)
Yes, I noticed - sorry to trouble you, and thank's for a great article.

Not a problem. Thanks for the compliment. I hope you are able to use the information provided.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

the sqlist (9/22/2009)
Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values

Actually the start/end/increment values don't quite work because of this little code fragment

where
number between @pStartValue and @pEndValue
and number % @pIncrement = 0

that last line should have been

and (number - @pStartValue) % @pIncrement = 0

so that it's possible to get, for example, a sequence like 5,8,11,14

I haven't actually checked the code, just skim-read it, so I may have missed some other omissions.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Great article, Lynn. Extremely interesting, and good clear testing report and data.

All those people querying why generate it dynamically amaze me - I wouldn't dream of having a static pre-generated 256 million row tally table in any system I was responsible for (but yes, I do have one with 11k rows, which is useful for many things) so dynamic generation is the only option if I ever need one that big. Also, from a performance point of view reading a big static Tally table is going to cause head movement on mty discs, whereas using a table valued function may enable me to avoid that IO overhead, so I have at least a chance that dynamic generation will reduce disc load compared to a static table.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Jeff Moden (10/2/2009)
--===== Find the "gap ranges" -- This assumes that gaps include any whole number greater than 0 SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1 FROM #MyTest b WHERE b.MyID < a.MyID), GapEnd = MyID - 1 FROM #MyTest a
WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)
It also doesn't need a mega-row tally table.

Why do I think the last line of that code should read WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest) and a.MyID > 1

edit: I remember now why I hate html, at least with code sets other than unicode. It is something to do with "&" followed by "gt;" insteard of ">" and other similar inanities.

Tom

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 10

Just thought I'd try this, I noticed that the description says "The second CTE, named L1, uses the BaseNum CTE to generate a second result set with one thousand rows. It accomplishes this by crossing join BaseNum with itself two times." but the code only has one cross join so only produces 100 rows not 1,000.