Understanding and Using APPLY (Part 1)

Question

Understanding and Using APPLY (Part 1)

Viewing 15 posts - 61 through 75 (of 89 total)

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

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 1

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 SSC Guru Points: 150468 More actions · Answer 2

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
All articles available on SQL.kiwi
@SQL_Kiwi

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 3

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

https://sqlroadie.com/

doodlingdba Hall of Fame Points: 3164 More actions · Answer 4

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 SSC Guru Points: 150468 More actions · Answer 5

Thanks, Doodles 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

rob mcnicol SSC Eights! Points: 834 More actions · Answer 6

rob mcnicol

SSC Eights!

Points: 834

December 21, 2010 at 12:02 am

#1264907

choice names for the students

chur!

Paul White SSC Guru Points: 150468 More actions · Answer 7

rob mcnicol (12/21/2010)
choice names for the students
chur!

Best comment so far! :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 8

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

Jared
CE - Microsoft

Paul White SSC Guru Points: 150468 More actions · Answer 9

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
All articles available on SQL.kiwi
@SQL_Kiwi

Jared SSC Guru Points: 61793 More actions · Answer 10

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

Jared
CE - Microsoft

Tom257 SSChampion Points: 11543 More actions · Answer 11

This is very good Paul.

Concise and Clear, not too long and with easily understandable examples.

Thanks for taking the time to create this article. I appreciate your efforts.

10 minutes learning something new in the morning is a great way to start my day.

Paul White SSC Guru Points: 150468 More actions · Answer 12

Thanks, Tom.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

reinpost Grasshopper Points: 17 More actions · Answer 13

Just a trivial remark: right at the start it says

APPLY is named after the process of applying a set of input rows to a table-valued function.

Surely you mean to say applying a table-valued function to a set of input rows.

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 14

Really Nice article ..looking forward for more...

good luck..

Paul White SSC Guru Points: 150468 More actions · Answer 15

reinpost (1/6/2012)
Just a trivial remark: right at the start it says
APPLY is named after the process of applying a set of input rows to a table-valued function.
Surely you mean to say applying a table-valued function to a set of input rows.

I think it makes sense either way! 😎

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi