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 Sunday, October 4, 2009 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 4:34 PM
Points: 3, Visits: 6
Clearly you did not understand the code. COUNT returns one row. My code returns the vector 0..N-1 based on the size of the target table.

the sqlist (9/22/2009)
laughingskeptic (9/22/2009)
One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

declare @i bigint
set @i = 0
select @i=@i+1 as num from largest_table





What happened to the good old count:

declare @i bigint
select @i=count(*) from largest_table

Post #797561
Posted Sunday, October 4, 2009 1:04 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
laughingskeptic (10/4/2009)
Clearly you did not understand the code. COUNT returns one row. My code returns the vector 0..N-1 based on the size of the target table.

I've got to say - I think it's you who did not understand the code. Firstly, it's a scalar variable, secondly the other code does exactly the same, just minus the rbar.

Of course, it would be far more efficient to query sysindexes for the largest value anyway, then add maybe 5% to that number to account for the innaccuracies inherent therein.


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #797580
Posted Sunday, October 4, 2009 4:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
Matt Whitfield (10/4/2009)
laughingskeptic (10/4/2009)
Clearly you did not understand the code. COUNT returns one row. My code returns the vector 0..N-1 based on the size of the target table.

I've got to say - I think it's you who did not understand the code. Firstly, it's a scalar variable, secondly the other code does exactly the same, just minus the rbar.

Of course, it would be far more efficient to query sysindexes for the largest value anyway, then add maybe 5% to that number to account for the innaccuracies inherent therein.

Agreed

The posted code does not return a 'vector' (set?) at all, it just increments the value of @i once for every row. That was the basis for sqllists very reasonable question.

laughingskeptic, you need to be very sure that what you are posting is bullet-proof before making such bold statements. Otherwise, you risk coming off as an arrogant twit.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #797605
Posted Sunday, October 4, 2009 4:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
My first addiction was to the cartridge version (woot) of Galaxian on the VIC-20. Awesome game.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #797606
Posted Monday, October 5, 2009 3:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, Visits: 719
The first game I played was BMX Simulator for the C64 I think... Or maybe it was Way of the Exploding Fist... either way, I don't really have a 'claim to fame' on that front!

I did start programming when I was 6 though!


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #797732
Posted Monday, October 5, 2009 5:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 36,978, Visits: 31,498
laughingskeptic (10/4/2009)
Jeff Moden (9/22/2009)
That's nice, but let's see you use that in a join or even as a stand-alone result set.


A table valued function will cover this if you don't want the variable exposed in the current scope.


So, let's see the code to do that.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #797797
Posted Monday, October 5, 2009 7:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, Visits: 428
Can't remember the first game I played. It would have been on a Vic20 at a friends house because we couldn't afford computers.

My first introduction to anything resembling programming was at 7 or 8. Turtle Program for the BBC Micro. We had it at school and had the robotic turtle and everything. It was Ace. Mum got a BBC shortly after and I'd spend the evenings prepping scripts of complex patterns to take into school and draw with the robot.


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #797887
Posted Monday, October 5, 2009 8:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 4,348, Visits: 6,157
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!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #797923
Posted Monday, October 5, 2009 8:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, Visits: 428
Data Error! Please rewind and try again.

_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #797936
Posted Monday, October 5, 2009 8:36 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 9:33 PM
Points: 23,277, Visits: 31,997
I started programming in Junior High. Not even on a "real" computer, an HP 9810 (a glorified programmable calculator) that my grandfather purchased for his medical lab. First was just modifying existing code to not waste paper. Then I actually started writing some original code for him based on the algorithims he provided. He even provided test data and expected results. He was doing it right and I wasn;t the one teaching him, he taught me.

Edit: Here is some info about the HP 9810.



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 #797956
« Prev Topic | Next Topic »

Add to briefcase «««1213141516»»

Permissions Expand / Collapse