Stairway to Advanced T-SQL Level 2: Using the APPLY Operator

Question

Stairway to Advanced T-SQL Level 2: Using the APPLY Operator

Greg Larsen

SSC-Insane

Points: 20961
More actions
January 28, 2015 at 12:00 am

#293805

Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 2: Using the APPLY Operator
Gregory A. Larsen, MVP

Viewing 15 posts - 1 through 15 (of 15 total)

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

DrKiller Right there with Babe Points: 780 More actions · Answer 1

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.

Chris Simon SSC Enthusiast Points: 104 More actions · Answer 2

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

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 3

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?

g.britton SSChampion Points: 13863 More actions · Answer 4

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

jonesboy21 Right there with Babe Points: 770 More actions · Answer 5

jonesboy21

Right there with Babe

Points: 770

January 28, 2015 at 8:38 am

#1773712

nice article

Christian Bahnsen SSC Eights! Points: 969 More actions · Answer 6

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

March 30, 2016 at 6:29 am

#1868560

Good article, thanks.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 8

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?

Indy Mike Mr or Mrs. 500 Points: 518 More actions · Answer 9

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?

darius.england Mr or Mrs. 500 Points: 557 More actions · Answer 10

Please don't create questions in such a way that from one question one can deduce the correct answer(s) to the others. Thanks.

darius.england Mr or Mrs. 500 Points: 557 More actions · Answer 11

Please don't create questions in such a way that from one question one can deduce the correct answer(s) to the others. Thanks.

paul_milligan01 SSC Enthusiast Points: 155 More actions · Answer 12

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.

kaj SSCommitted Points: 1599 More actions · Answer 13

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.

Test User SSC Enthusiast Points: 181 More actions · Answer 14

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;