[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Awesome job, Chris. Well done!

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Interesting, entertaining and educational with worked examples and performance statistics make this a great read.

The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.

Great job Chris. Thanks for sharing.

.

• Ever since discovering a use for APPLY for the first time approximately 2 years ago, I've found I use it almost everywhere these days.

To the extent where I start looking for candidates for re-writing using APPLY! --> thankfully I'm about to move onto pastures new so the 7 years worth of stored procedures and queries I have here will no longer be candidates for such an exercise!

APPLY truly is a remarkable technique and this article adds another strong argument to the reasons why you should have this in your arsenal - if you don't yet feel confident working with APPLY work through the examples here and you'll see how powerful it is.

thanks for another great article and keep up the good work

• Jeff Moden (4/16/2013)

Awesome job, Chris. Well done!

Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it π

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Tim Walker. (4/16/2013)

Interesting, entertaining and educational with worked examples and performance statistics make this a great read.

The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.

Great job Chris. Thanks for sharing.

Thank you very much, Tim. Almost every day, something comes up on ssc which requires a cCA or some other manipulation of APPLY. I don't know what we did without it.

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• balde (4/16/2013)

Ever since discovering a use for APPLY for the first time approximately 2 years ago, I've found I use it almost everywhere these days.

To the extent where I start looking for candidates for re-writing using APPLY! --> thankfully I'm about to move onto pastures new so the 7 years worth of stored procedures and queries I have here will no longer be candidates for such an exercise!

APPLY truly is a remarkable technique and this article adds another strong argument to the reasons why you should have this in your arsenal - if you don't yet feel confident working with APPLY work through the examples here and you'll see how powerful it is.

thanks for another great article and keep up the good work

Thank you balde, and best wishes with your new venture.

[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• In the initial CREATE TABLE example, [RowNum] should be [EmployeeId].

Thanks for the great article.

Regards,

Gary

• Gary Nease (4/16/2013)

In the initial CREATE TABLE example, [RowNum] should be [EmployeeId].

Thanks for the great article.

Thank you very much Gary - duly updated.

[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• ChrisM@Work (4/16/2013)

Tim Walker. (4/16/2013)

Interesting, entertaining and educational with worked examples and performance statistics make this a great read.

The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.

Great job Chris. Thanks for sharing.

Thank you very much, Tim. Almost every day, something comes up on ssc which requires a cCA or some other manipulation of APPLY. I don't know what we did without it.

For single row things, we use to use "Correlated Sub-Queries" in the SELECT list. Of course, those didn't have the flexibility of returning multiple rows like Cross Apply does.

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• ChrisM@Work (4/16/2013)

Jeff Moden (4/16/2013)

Awesome job, Chris. Well done!

Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it π

Heh... I just knew what you were capable of but, ultimately, my opinion doesn't matter. The proof of what you're capable of is now officially documented. More than 7K reads in the first 2 days and 5 stars on the very first try. I say again, well done, ol' friend!

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden (4/16/2013)

ChrisM@Work (4/16/2013)

Jeff Moden (4/16/2013)

Awesome job, Chris. Well done!

Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it π

Heh... I just knew what you were capable of but, ultimately, my opinion doesn't matter. The proof of what you're capable of is now officially documented. More than 7K reads in the first 2 days and 5 stars on the very first try. I say again, well done, ol' friend!

I concur on Jeff's congratulations and on a fine article. Yours is a talent that should be shared so I'm looking forward to egging you on to even more... π

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?

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

• You're not doing so bad, yourself, Dwain. Ten mostly 5 star articles in a year is freakin' awesome!

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden (4/17/2013)

You're not doing so bad, yourself, Dwain. Ten mostly 5 star articles in a year is freakin' awesome!

Thanks Jeff.

Actually my latest only got 4 stars but on the other hand it passed 10K views over the weekend and is now my #1 most read article! So I'm a bit happy about that.

Now if I could just get my current effort out of its stall.

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?

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

• Great post!! thanks for sharing..it is really very useful

--Divya

Viewing 15 posts - 1 through 15 (of 38 total)