Understanding and Using APPLY (Part 1)

Question

Understanding and Using APPLY (Part 1)

Viewing 15 posts - 31 through 45 (of 89 total)

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

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

JJ B (4/12/2010)
This is the best article on APPLY that I've read. I particularly appreciate the paragraph: "APPLY calls the function once for each row from the input. ...to produce the eventual full result." It is very well worded and clear.

Thank you so much. I am glad you found the explanation helpful - it took me quite some time to decide on wording in a number of places, and again, I have to thank the people that contributed their thoughts on the first drafts here.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

colgateway SSC Enthusiast Points: 116 More actions · Answer 2

Hi. I'm just learning t-sql and may be missing something obvious but I cannot get this part to run without errors. I first tried creating the tables by hand and inputting code as I went along. Then I downloaded your code and ran it. Same exact error.

This is the code:

SELECT S.subject_id,

iTVF.student_id,

iTVF.grade_score

FROM dbo.Subject S

CROSS

APPLY dbo.GetTopStudentsBySubject(S.subject_id, 2)

ORDER BY

S.subject_id ASC,

iTVF.student_id ASC;

GO

This is the error:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '.'.

Thanks.

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

colgateway (4/12/2010)
...Then I downloaded your code and ran it. Same exact error.

The code you used doesn't quite match 'Query 08' in the download file - did you edit it?

The published code for Query 08 looks like this:

SELECT S.subject_id,

iTVF.student_id,

iTVF.grade_score

FROM dbo.Subject S

CROSS

APPLY dbo.GetTopStudentsBySubject(S.subject_id, 2) iTVF

ORDER BY

S.subject_id ASC,

iTVF.student_id ASC;

That's almost the same as the code you posted - but you were missing the "iTVF" alias name, which is definitely there in the file. Perhaps download the sample again?

It produces the following output:

subject_id student_id grade_score

1 1 84

1 2 77

2 1 72

2 3 79

3 3 86

3 4 81

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

colgateway SSC Enthusiast Points: 116 More actions · Answer 4

I reopened, copied, ran it again and it works just like you said. I have no idea how I deleted the alias. Thanks for responding. Now I just have to read through this about 10 more times. Thanks again.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Doggoneit! Does everyone one this site have better graphics than me? :angry:

Great article too, Paul. Congrats. 🙂

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

RBarryYoung (4/12/2010)
Doggoneit! Does everyone one this site have better graphics than me? :angry:

The job on the graphics was quite good. A lot of kudos are deserved for that.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

RBarryYoung (4/12/2010)
Doggoneit! Does everyone one this site have better graphics than me? :angry:
Great article too, Paul. Congrats. 🙂

I don't. I can barely include a screenshot. I have no clue how to do graphics and no eye for what looks good when I am writing. I don't think in pictures, I think in words.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jack Corbett (4/13/2010)
RBarryYoung (4/12/2010)
Doggoneit! Does everyone one this site have better graphics than me? :angry:
Great article too, Paul. Congrats. 🙂
I don't. I can barely include a screenshot. I have no clue how to do graphics and no eye for what looks good when I am writing. I don't think in pictures, I think in words.

Ah, thanks Jack. Now I feel better ... 😀

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

korolana Valued Member Points: 68 More actions · Answer 9

Nice article, but what is an advantage to use this approach vs simple join?

From the performance point of view I don't think that calling UDTF on every row of the table is a good idea.

Also for readability to read one simple join is much quicker than a) to read a join b) to read the function c) mentally "to link" both.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

korolana (4/13/2010)
Nice article, but what is an advantage to use this approach vs simple join?
From the performance point of view I don't think that calling UDTF on every row of the table is a good idea.
Also for readability to read one simple join is much quicker than a) to read a join b) to read the function c) mentally "to link" both.

As an example, try using a nice iTVF split function against a table column full of CSVs and see. A "simple" inner join just won't cut the mustard there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

korolana (4/13/2010)
Nice article, but what is an advantage to use this approach vs simple join?
From the performance point of view I don't think that calling UDTF on every row of the table is a good idea.
Also for readability to read one simple join is much quicker than a) to read a join b) to read the function c) mentally "to link" both.

The short answer is that they do different things. If you use a TVF in a JOIN, it's parameters have to be constant for the whole query (or subquery), so the TVF is only called once and only one rowset is returned.

However, if you use an APPLY instead, then the TVF parameters do not have to be constant, and they can have different values for every row of the preceding table(s). Specifically, this means that you can use column values from the first table as parameters into the TVF. The cost of this is that of course you have to call the TVF for every row of the input, but there's no way around this cost if you want that functionality.(*)

*(except that if some of the calls would have duplicate parameter values, the optimizer is allowed to skip the call to the TVF and just re-use the prior rowset returned for the matching parameters).

[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: 150468 More actions · Answer 12

korolana (4/13/2010)
Nice article, but what is an advantage to use this approach vs simple join?
From the performance point of view I don't think that calling UDTF on every row of the table is a good idea.
Also for readability to read one simple join is much quicker than a) to read a join b) to read the function c) mentally "to link" both.

Part one is all about introducing a useful way to think about solving some kinds of complex problems using APPLY. Many times, breaking the logical problem up into a series of simpler steps makes it easier to find a solution.

As I will explain in Part Two, using APPLY does not necessarily mean that SQL Server will literally call the function once per row - even though the query might logically be written that way.

Read Part Two next week, and hopefully things will become clearer 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

karyn.webb SSChasing Mays Points: 618 More actions · Answer 13

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 🙂

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 14

Thank you Paul for excellent article, I thoroughly enjoyed reading it. Looking forward to read Part II next week. I usually start using new language features pretty soon after they arrive, but APPLY somehow fell out due to one call for every row in the input feature which somewhat scared me.

It is great that you will have it all covered next week so I can have my doubts go away.

Oleg

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 15

Oleg Netchaev (4/13/2010)
Thank you Paul for excellent article, I thoroughly enjoyed reading it. Looking forward to read Part II next week. I usually start using new language features pretty soon after they arrive, but APPLY somehow fell out due to one call for every row in the input feature which somewhat scared me.
It is great that you will have it all covered next week so I can have my doubts go away.
Oleg

I have the same impression. Even though, I used APPLY to return comma seperated value from one column (Subject Name), grouped by on another column (Student Name).

Looking forward for Part II, to know more in and out of APPLY.

Nizam.