The Dynamic Tally or Numbers Table

Question

The Dynamic Tally or Numbers Table

Viewing 15 posts - 121 through 135 (of 159 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Matt Whitfield (10/2/2009)
When I was at school there used to be a game called 'Betrayal at Krondor' - where you would meet people as you travelled around. They might have a certain skill, like lockpicking, and you would pay them x amount of whatever-currency-it-was-in-the-game, then you would see a very jerky video, after which a message box would pop up saying 'your lock picking skill has just increased 12 points' - and you'd be all like WTF - but somehow you were better at lock picking.
I feel like you've just said 'your SQL skill has just increased 12 points' - and now I need to go and study that!
Thank you 😀

Jerky video? Silly young'ins... we had similar games, 'cept when you did that type of stuff you might just maybe have seen some ascii art, but for the most part we just got the line "your skill has just advance or whatever"...

Thanks for reminding me just how quickly I'm aging...

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Boy, the video game chatter has me realizing how long in the tooth I am getting as well! sigh...:cool:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Jeff, try your sample code without your beloved clustered PK. That eager index spool eats you alive. 74.5 cost and 2.6M IOs. The real world rarely has the 'optimization limitations' you carry in your samples. :blink:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

TheSQLGuru (10/2/2009)
Jeff, try your sample code without your beloved clustered PK. That eager index spool eats you alive. 74.5 cost and 2.6M IOs. The real world rarely has the 'optimization limitations' you carry in your samples. :blink:

I must admit I tried that too. I'm a bit busy to go into it fully right now, but I definitely think there's something to be learned there. I just need to figure out what it is 😀 Any input gratefully received.

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

The key thing to be learned here is that optimal varies GREATLY depending on the situation! Having many tricks up your sleeve for solving tsql problems (especially the common and/or very problematic ones) increases the probability that you will be able to provide the best solution to your problem on your (or like me your client's) system.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Matt Whitfield (10/2/2009)
When I was at school there used to be a game called 'Betrayal at Krondor' - where you would meet people as you travelled around. They might have a certain skill, like lockpicking, and you would pay them x amount of whatever-currency-it-was-in-the-game, then you would see a very jerky video, after which a message box would pop up saying 'your lock picking skill has just increased 12 points' - and you'd be all like WTF - but somehow you were better at lock picking.
I feel like you've just said 'your SQL skill has just increased 12 points' - and now I need to go and study that!
Thank you 😀

Heh... that's why I comment the code... keeps the video from jerking around. 😛

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

TheSQLGuru (10/2/2009)
The key thing to be learned here is that optimal varies GREATLY depending on the situation! Having many tricks up your sleeve for solving tsql problems (especially the common and/or very problematic ones) increases the probability that you will be able to provide the best solution to your problem on your (or like me your client's) system.

Absolutely spot on.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

I've had a lot of moments like that in here, Matt 🙂 I'm not sure if it equates to +1 wisdom, or +1 intelligence, but one day I hope to level up.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 9

Bob Hovious 24601 (10/2/2009)
I've had a lot of moments like that in here, Matt 🙂 I'm not sure if it equates to +1 wisdom, or +1 intelligence, but one day I hope to level up.

I'll drink to that 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

BTW... you can also use a WHERE NOT EXISTS against a Tally table and it'll be fast as well. Just don't try the ol' outer join with a NULL detector in the WHERE clause because it will be slow. Still, it will require a large enough Tally table to cover the range size you need where the other method I posted doesn't.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Lynn Pettis (10/2/2009)
On my dev system master.sys.all_parameters returns 6776 rows. In the context of the particular database I tested it, sys.all_parameters, returned 6841.

That's why I try to stick the master.sys.system_columns, it's generally much more consistent (especially on my systems).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Luke L (10/2/2009)
Matt Whitfield (10/2/2009)
When I was at school there used to be a game called 'Betrayal at Krondor' - where you would meet people as you travelled around. They might have a certain skill, like lockpicking, and you would pay them x amount of whatever-currency-it-was-in-the-game, then you would see a very jerky video, after which a message box would pop up saying 'your lock picking skill has just increased 12 points' - and you'd be all like WTF - but somehow you were better at lock picking.
I feel like you've just said 'your SQL skill has just increased 12 points' - and now I need to go and study that!
Thank you 😀
Jerky video? Silly young'ins... we had similar games, 'cept when you did that type of stuff you might just maybe have seen some ascii art, but for the most part we just got the line "your skill has just advance or whatever"...
Thanks for reminding me just how quickly I'm aging...
-Luke.

Heh, you don't even want to know what my first video game was. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

RBarryYoung (10/2/2009)
Luke L (10/2/2009)
Matt Whitfield (10/2/2009)
When I was at school there used to be a game called 'Betrayal at Krondor' - where you would meet people as you travelled around. They might have a certain skill, like lockpicking, and you would pay them x amount of whatever-currency-it-was-in-the-game, then you would see a very jerky video, after which a message box would pop up saying 'your lock picking skill has just increased 12 points' - and you'd be all like WTF - but somehow you were better at lock picking.
I feel like you've just said 'your SQL skill has just increased 12 points' - and now I need to go and study that!
Thank you 😀
Jerky video? Silly young'ins... we had similar games, 'cept when you did that type of stuff you might just maybe have seen some ascii art, but for the most part we just got the line "your skill has just advance or whatever"...
Thanks for reminding me just how quickly I'm aging...
-Luke.
Heh, you don't even want to know what my first video game was. 🙂

lemme guess, Pong. 😉

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Lynn Pettis (10/2/2009)
RBarryYoung (10/2/2009)
Luke L (10/2/2009)
Jerky video? Silly young'ins... we had similar games, 'cept when you did that type of stuff you might just maybe have seen some ascii art, but for the most part we just got the line "your skill has just advance or whatever"...
Thanks for reminding me just how quickly I'm aging...
-Luke.
Heh, you don't even want to know what my first video game was. 🙂
lemme guess, Pong. 😉

Dude, I played Spacewar in an arcade at the shore, circa 1970.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Cool. It was released in 1962 and ran initially on the PDP-1. Was used by the FE's when setting up the PDP-1 as a final test before turning over the systems to the customer (if you can believe Wikipedia). Pong didn't come out until 1972.