Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

Understanding and Using APPLY (Part 1) Expand / Collapse
Author
Message
Posted Tuesday, April 20, 2010 8:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 5, 2014 2:22 PM
Points: 1,586, Visits: 1,860
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.
Post #906885
Posted Tuesday, April 20, 2010 5:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #907331
Posted Friday, May 7, 2010 12:22 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:59 PM
Points: 406, Visits: 780
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
Post #917652
Posted Wednesday, December 15, 2010 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 9, 2013 9:36 AM
Points: 35, Visits: 178
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
Post #1035111
Posted Wednesday, December 15, 2010 8:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
Thanks, Doodles



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1035189
Posted Tuesday, December 21, 2010 12:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 3:18 AM
Points: 149, Visits: 565
choice names for the students

chur!
Post #1037523
Posted Tuesday, December 21, 2010 12:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
rob mcnicol (12/21/2010)
choice names for the students

chur!

Best comment so far!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1037542
Posted Tuesday, October 18, 2011 9:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1192269
Posted Tuesday, October 18, 2011 11:39 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:47 AM
Points: 9,928, Visits: 11,205
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1192377
Posted Tuesday, October 18, 2011 11:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 4, 2014 10:09 AM
Points: 2,706, Visits: 3,427
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1192378
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse