Paul White NZ (4/12/2010)
Nice Article Paul. It is very well expalined with example.
I have a doubt, since the UDTF would get executed as many times as the row count of primary resultset (Student's Count). So, would it not affect the performamance. Because this same can be achived by a simple join and that get executed in a set. (I know, you have used this example for illustration purpose. :-)).
For maintenability and readibility, this concept is fine enough.
Well, Please suggest that which is better approach in terms of performance:
Use of UDTF with CROSS APPLY (to a certain extent, which cannot be achived by simple SQL)
or Putting all tables / view in the final SQL
Thanks - you raise some interesting questions, which will be fully addressed in part 2, next week 😎
My questions are on the same lines. Is it always better to use APPLY than a JOIN?
However I think the 'UDTF would get executed as many times as the row count of primary resultset' and 'simple join' comments are pretty superficial. JOIN is not as simple as it looks is what execution plans tell me. Thanks to some good articles here.
Paul, my main question is why use APPLY instead of a JOIN?
I tried out Query 04, Query 05 and the following query (the one in the article).
FROM dbo.GradeScore GS
JOIN dbo.Subject J
ON J.subject_id = GS.subject_id
JOIN dbo.Student S
ON S.student_id = GS.student_id
WHERE S.gender = N'M'
They all showed me the same execution plan. Am I not 'seeing' something?
PS: A little knowledge is very dangerous 🙂
I got an error (sniff sniff) when i tried a preview and had to type the whole thing again. I will do a Ctrl + A, Ctrl + C next time before I hit the button.