SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding and Using APPLY (Part 1)


Understanding and Using APPLY (Part 1)

Author
Message
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1903 Visits: 1950
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.


Can you share some examples of other ways to use APPLY. I would definitely like to learn some other techniques. Thanks.
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
Carla Wilson-484785 (4/20/2010)
Can you share some examples of other ways to use APPLY. I would definitely like to learn some other techniques. Thanks.

Take a look in the download files for both parts, and be sure to read part 2 of the article as well.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Arjun Sivadasan
Arjun Sivadasan
Say Hey Kid
Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)Say Hey Kid (664 reputation)

Group: General Forum Members
Points: 664 Visits: 976
I haven't got around to read the 2nd part of the article completely. I did have a glance. I just want to thank you again as what i learned here helped me to optimize a query by a great margin. You Rock! :-)

-arjun
doodlingdba
doodlingdba
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 213
Just wanted to express my thanks for putting this article together! You got the concept of CROSS APPLY across perfectly. I'm grateful to all you DBA/DEV boddhisattvas out there sharing the knowledge! :-)

Doodles
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
Thanks, Doodles :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
rob mcnicol
rob mcnicol
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 686
choice names for the students

chur!
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
rob mcnicol (12/21/2010)
choice names for the students

chur!

Best comment so far! Laugh



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4962 Visits: 3694
Great article (although I am a little late getting to read it)!

A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.

Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?

Just trying to find out more of an answer about "functions" here versus the subquery route.

Thanks,

Jared

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15550 Visits: 11355
jared-709193 (10/18/2011)
A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.

Hi Jared,

In-line TVFs are like (parameterized) views in that the definition of the function is expanded into the query text before optimization. A query that uses an in-line TVF will be optimized the same as if you wrote the function text out by hand as a subquery.

Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?

There's quite a lot of good information about functions in Books Online, for example: Types of Functions (link)

A multi-statement TVF definition contains the keywords BEGIN and END (as do scalar user-defined functions) and more than one statement. In-line TVFs use the RETURNS TABLE syntax to introduce a single SELECT. In-line functions do not use the BEGIN/END combination.

Multi-statement functions (and their scalar relations) are often, though not always, a performance drag for many reasons. One reason is that only in-line TVFs are expanded into the parent query for optimization. Scalar UDFs can be especially troublesome because they are executed once per row, in a new T-SQL context, and cannot use parallelism.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4962 Visits: 3694
SQL Kiwi (10/18/2011)
jared-709193 (10/18/2011)
A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.

Hi Jared,

In-line TVFs are like (parameterized) views in that the definition of the function is expanded into the query text before optimization. A query that uses an in-line TVF will be optimized the same as if you wrote the function text out by hand as a subquery.

Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?

There's quite a lot of good information about functions in Books Online, for example: Types of Functions (link)

A multi-statement TVF definition contains the keywords BEGIN and END (as do scalar user-defined functions) and more than one statement. In-line TVFs use the RETURNS TABLE syntax to introduce a single SELECT. In-line functions do not use the BEGIN/END combination.

Multi-statement functions (and their scalar relations) are often, though not always, a performance drag for many reasons. One reason is that only in-line TVFs are expanded into the parent query for optimization. Scalar UDFs can be especially troublesome because they are executed once per row, in a new T-SQL context, and cannot use parallelism.


Great! Thanks for the quick response Paul.

Jared

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search