Understanding and Using APPLY (Part 2)

Question

Post reply

Understanding and Using APPLY (Part 2)

Paul White

SSC Guru

Points: 150467
More actions
April 17, 2010 at 11:16 am

#223532

Comments posted to this topic are about the item Understanding and Using APPLY (Part 2)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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

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

I would like to express my sincere thanks to the following people, for their help in producing this article:

Amanda Lawrence

Jeff Moden MVP

Chris Morris

Lutz Müller

Jason Brimhall[/url]

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

peter-757102 SSCertifiable Points: 6877 More actions · Answer 2

Excellent and with enough extra material to make it interesting for those already familiar with apply!

clementhuge SSC-Addicted Points: 475 More actions · Answer 3

Very interesting. I alrady use cross apply (mostly with table-valued functions) and I use a lot of xqueries. I like the last t-sql code about xquery. I was not using aliases and also navigating with rowset and the apply functions. Good tip!

Clement

Mike M - DBA2B Right there with Babe Points: 764 More actions · Answer 4

Thank you once again, Paul. I'll be experimenting today.

I just noticed something that I think is in error:

The first CROSS JOIN example under your "Equivalent Expressions Using APPLY and JOIN" header performs "ORDER BY S.name ASC, S.name ASC". Is that supposed to be "S.name ASC, J.name ASC"?

Regards,

Mike M

greenclay SSC Enthusiast Points: 109 More actions · Answer 5

Thanks for this article. There's a lot to consider here.

In the CROSS JOIN section, the query

[font="Courier New"]SELECT S.name, J.name

FROM dbo.Student S

CROSS

JOIN dbo.Subject J

ORDER BY S.name ASC, S.name ASC;

[/font]

should read

[font="Courier New"]SELECT S.name, J.name

FROM dbo.Student S

CROSS

JOIN dbo.Subject J

ORDER BY S.name ASC, J.name ASC;[/font]

should it not?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 6

A thorough and well explained article. The supplemental file was a real bonus. Thanks.

WayneS SSC Guru Points: 95460 More actions · Answer 7

Paul - another excellent article on using APPLY. Thank you so much for doing this... now I need to find the time to disect these two articles, and get a good understanding of them.

Again, thanks for doing these.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 8

Very well written article. Thanks Paul.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Tom Garth SSCertifiable Points: 6193 More actions · Answer 9

I will be using APPLY beginning this week. Thanks for a really understandable series.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

ckuroda Mr or Mrs. 500 Points: 511 More actions · Answer 10

This is an excellent follow up to the first article. In the example for displaying a comma separated values list of all the test scores for each student, could you have also used the coalesce function to prevent the first comma in the output?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Paul White NZ (4/18/2010)
I would like to express my sincere thanks to the following people, for their help in producing this article:
Amanda Lawrence
Jeff Moden MVP
Chris Morris
Lutz Müller
Jason Brimhall[/url]
Paul

You're welcome.

It is a good article - and it was a pleasure to help.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 12

Excellent job, Paul. It helped me learn a few things about a command I rarely use.

LutzM SSC Guru Points: 107049 More actions · Answer 13

Great article series, Paul!

It was a pleasure being selected to get a preview and to give some thoughts/comments.

Let's see how long it takes until I have referenced those two articles more often then I did Jeffs CrossTab/DynamicCrossTab 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

UMG Developer SSChampion Points: 13482 More actions · Answer 14

Paul, Wow, thanks for these two articles they really helped me understand better use of APPLY. Now if I can just remember all of that I think I will use it a lot more.

Thanks again!