January 28, 2015 at 12:00 am
Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 2: Using the APPLY Operator
Gregory A. Larsen, MVP
January 28, 2015 at 2:00 am
Very nice article and it clearly explains what Apply is used for!
I feel that you should've included some "tricks" to improve performance on big queries to see the real benefit of the function.
I've read before that converting a SCALAR function to TABLE function can improve performance when using the CROSS APPLY as it brings in some parallelism to the query plan.
January 28, 2015 at 2:11 am
Thanks for the article, it explains the APPLY in a simple and straightforward manner.
As a newby, could you explain to me why and when you would use :
SELECT * FROM dbo.SearchString as S
CROSS APPLY
(SELECT ProductName, Price
FROM dbo.Product
WHERE ProductName like '%' + S.String + '%') as X
instead of
SELECT * FROM dbo.SearchString as S
inner join
(SELECT ProductName, Price
FROM dbo.Product ) as X
on ProductName like '%' + S.String + '%'
Thanks
January 28, 2015 at 4:33 am
A very interesting article, explaining the concept very well.
+1 on Chris Simon's question though. I ran both versions and the equivalent query using the function and got 3 identical execution plans.
Would I be right in thinking that this is one of those times when the simple examples used for demonstration purposes don't really do justice to the concept that is being explained?
Edit: Ignore that, thinking it through there will obviously be plenty of times when you only have a function to hand, which was kind of the main point of the article 🙂 I am starting to see a lot of potential to make use of this. Maybe my imagination (or experience) is not so good but I can see fewer reasons to use it with table valued expressions over a join... yet.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
January 28, 2015 at 6:58 am
One of my favorite uses for APPLY is to make my queries DRY-er by pre-computing subexpressions. e.g.
with data as (select * from (values
(1,2,3),
(4,5,6)
) v(a,b,c)
)
select a, b, f.future, d.diff, p.past
from data
cross apply (select dateadd(day, c, getdate())) f(future)
cross apply (select datediff(day, getdate()-b, f.future)) d(diff)
cross apply (select dateadd(day, -d.diff, f.future)) p(past)
You can push your complicated expressions in to the APPLYs and refer to them by name instead of retyping (thus, DRY!)
Gerald Britton, Pluralsight courses
January 28, 2015 at 8:38 am
nice article
February 4, 2015 at 7:28 am
Great article! The "Using a Table-valued Expression" example was spot-on for a thorny problem I'd been working on. I adapted your example and came up with a solution. APPLY is a great tool to add to my workbelt. Thanks
March 30, 2016 at 6:29 am
Good article, thanks.
March 30, 2016 at 9:31 am
g.britton (1/28/2015)
One of my favorite uses for APPLY is to make my queries DRY-er by pre-computing subexpressions. e.g.
with data as (select * from (values
(1,2,3),
(4,5,6)
) v(a,b,c)
)
select a, b, f.future, d.diff, p.past
from data
cross apply (select dateadd(day, c, getdate())) f(future)
cross apply (select datediff(day, getdate()-b, f.future)) d(diff)
cross apply (select dateadd(day, -d.diff, f.future)) p(past)
You can push your complicated expressions in to the APPLYs and refer to them by name instead of retyping (thus, DRY!)
Awesome! I've always daisy-chained a series of CTE's for this scenario.
with data as (select * from (values
(1,2,3),
(4,5,6)
) as v(a,b,c)
)
,Dry1 as
(select a,b,c, dateadd(day, c, getdate()) as future
from data)
,Dry2 as
(select a,b,c,future, datediff(day, getdate()-b, future) as diff
from Dry1)
,Dry3 as
(select a,b,c,future,diff, dateadd(day, -diff, future) as past
from Dry2)
select *
from Dry3
But now, I'll use your "DRY APPLY" method 😛
Curious, is the term "dry" one that you've made up or is this something I'm just not aware of?
March 31, 2016 at 12:36 pm
A question on performance. Let's say the applied TVF is deterministic. Will SQL Server cache the data returned by the function for the times when the exact same values are passed to it?
September 29, 2016 at 4:56 am
Please don't create questions in such a way that from one question one can deduce the correct answer(s) to the others. Thanks.
September 29, 2016 at 5:12 am
Please don't create questions in such a way that from one question one can deduce the correct answer(s) to the others. Thanks.
May 5, 2021 at 6:45 am
Interesting examples for using the APPLY and nice and succinct to. This is a comment on the final CROSS APPLY example and not a critique on the article itself.
The comment "I'll let you make that change and run the new code to verify it does produce the same output as Report 2"
This output in report 4 will be the same regardless of CROSS APPLY or OUTER APPLY due to the WHERE statement in the table values select. It would be similar to doing a LEFT JOIN but having the restriction in the WHERE clause.
May 5, 2021 at 11:42 am
One of my top use cases for APPLY is with a TOP(1), which is hard to achieve any other way, like for example selecting the latest price, i.e. something like this:
SELECT
it.ItemNbr,
it.ItemName,
pt.Price
FROM ItemsTable it
OUTER APPLY (
SELECT TOP(1)
Price
FROM PricesTable
WHERE
ItemNbr = it.ItemNbr
AND MaterialType = 1
ORDER BY
ChangeDate DESC
) pt
The descending ordering by date ensures that the latest relevant price is applied.
May 25, 2021 at 1:26 am
You can find the latest price with the row_number window function:
WITH tmp AS
(
SELECT
pt.ItemNbr,
pt.Price,
ROW_NUMBER() OVER (PARTITION BY pt.ItemNbr ORDER BY pt.ChangeDate DESC) AS rownum
FROM PricesTable AS pt
WHERE pt.MaterialType = 1
)
SELECT
it.ItemNbr,
it.ItemName,
pt.Price
FROM ItemsTable AS it
INNER JOIN tmp
ON it.ItemNbr = tmp.ItemNumber
WHERE tmp.rownum = 1;
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply