Understanding and Using APPLY (Part 1)

Question

Understanding and Using APPLY (Part 1)

Viewing 15 posts - 46 through 60 (of 89 total)

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

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

karyn.webb (4/13/2010)
Thank you Paul, really good reading and a great help in getting my head around yet another new concept. And they're all new right now so the clarity is very much appreciated.
Also - really nice to see Maori names! Kia ora 🙂

Kia ora! 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Excellent article, Paul! I've been trying to find a good explanation of APPLY since I first saw it in use about 4 months ago. For the first time I can "see" what's going on (BTW...great visuals) and I do believe I'm understanding it. Thanks!

Anxiously awaiting the second part...

Regards,

Mike M

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 3

Paul White NZ (4/12/2010)
I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?
There are some notes on this in the attached scripts - but essentially, no it is not required as such - but I do recommend it.
In general, I always schema-bind functions, unless there is a very good reason not to. Not only does it prevent the schema of referenced entities changing unexpectedly, it can also have significant optimization benefits.
If a function (scalar, iTVF, or multi-statement) is not marked as schema-bound, SQL Server marks it as non-deterministic, as performing user data access, and as performing system data access. This is a performance optimization - to avoid having to check the function and all dependent objects for these properties on every call.
This can have important performance consequences, since the Query Optimizer has many fewer plan options when it encounters a non-deterministic element. Marking a function as schema-bound forces the engine to statically check the function in detail to determine if it is deterministic or not.
Paul

Paul,

Outstanding article! Very well-written, and excellent discussion. I can't wait for part 2.

Keep up the great work! It's very much appreciated.

- Carla

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 4

The article is great and the pictures made it so much better! Other columnists should try to adapt this approach too!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

mishaluba (4/14/2010)
The article is great and the pictures made it so much better! Other columnists should try to adapt this approach too!

The problem with the articles here at SSC, is that unlike the forum posts, we have no way to see how someone's formatting tricks are done, so we cannot easily learn from each other :(.

[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]

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

RBarryYoung (4/14/2010)
The problem with the articles here at SSC, is that unlike the forum posts, we have no way to see how someone's formatting tricks are done, so we cannot easily learn from each other 🙁

No real trickery here - just screen-shots, Paint.NET, and time :satisfied:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Paul White NZ (4/14/2010)
RBarryYoung (4/14/2010)
The problem with the articles here at SSC, is that unlike the forum posts, we have no way to see how someone's formatting tricks are done, so we cannot easily learn from each other 🙁
No real trickery here - just screen-shots, Paint.NET, and time :satisfied:

Ah, I see now. You did all of your tables and code blocks as images too. Clever ...

[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]

TomThomson SSC Guru Points: 104773 More actions · Answer 8

I like the nice clear simple to understand approach, and the layout. Waiting eagerly for part 2.

Tom

Lempster SSCoach Points: 15651 More actions · Answer 9

I was writing a similar article (on some of the possibly lesser-known and/or used T-SQL features)to present to the rest of my team; I think I will scrap that and just point them at your article! 😉

Informative, concise and well-written.

Many thanks,

Lempster

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 10

Thanks for the excellent article! Clear and concise!

Providing sample code with extra examples was a perfect way to provide more information, without cluttering the main article.

I've never actually used APPLY before, but your article has given me a host of new ideas...

Looking forward to Part II!

grc-80104 Old Hand Points: 339 More actions · Answer 11

Great articles, Paul. Very clear and complete explanations and examples.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 12

This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."

It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions.

I often use an expression on the right side of the Apply operator. I wish there were more examples like this.

The right side of the expression can be a correlated subquery, for example, and that can be very useful.

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

David Walker-278941 (4/19/2010)
This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."
It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions.
I often use an expression on the right side of the Apply operator. I wish there were more examples like this.
The right side of the expression can be a correlated subquery, for example, and that can be very useful.

Yes, I walk a fine line differentiating 'function' from 'user-defined function', but I think overall the compromise on wording is a happy one. Please see part 2 (available now) for more examples of the type you seek.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TomThomson SSC Guru Points: 104773 More actions · Answer 14

David Walker-278941 (4/19/2010)
This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."
It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions.

It does say very clearly that this is article one of two, only the first half of the story. So I think it's unreasonable to complain that it doesn't have the whole story.

edit: spelling

Tom

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 15

OK, I get your point (both of you!)... I jsut get a little frustrated when so many examples fixate on one kind of thing. (For example, try to find beginning "teaching" examples that show two (or more) conditions on a Join statement, or conditions that use anything other than equality for a comparison. It's not your fault, but so many examples everywhere seem to be so monolithic!)

I think a tiny improvement would be to have Part 1 mention that other expressions such as correlated subqueries can also be used, and that you'll include examples in part 2.

Thanks for the article; it is helpful.

David