Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516

The Dynamic Tally or Numbers Table Expand / Collapse
Author
Message
Posted Tuesday, October 6, 2009 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 11,194, Visits: 11,137
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

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #798375
Posted Tuesday, October 6, 2009 3:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 7,004, Visits: 8,448
Give me a moment to whipe out a teardrop of nostalgia

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #798401
Posted Wednesday, December 9, 2009 6:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
Have tried to run it (copy + paste) but get errors.
What could I be doing wrong?


Post #831323
Posted Wednesday, December 9, 2009 6:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 23,253, Visits: 31,946
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #831362
Posted Wednesday, December 9, 2009 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 7:30 AM
Points: 37, Visits: 220
Yes, I noticed - sorry to trouble you, and thank's for a great article.
Post #831365
Posted Wednesday, December 9, 2009 7:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 23,253, Visits: 31,946
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #831380
Posted Tuesday, February 23, 2010 12:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 8,690, Visits: 9,226
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
Post #871386
Posted Tuesday, February 23, 2010 12:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 8,690, Visits: 9,226
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
Post #871394
Posted Tuesday, February 23, 2010 1:40 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 8,690, Visits: 9,226
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 &gt; 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
Post #871442
« Prev Topic | Next Topic »

Add to briefcase «««1213141516

Permissions Expand / Collapse