CROSS APPLY

Steve Jones compares writing a function versus using CROSS APPLY to write the same function:

The conclusion I’d take here is that CROSS APPLY ought to be a tool you keep in the front of your toolbox and use when you must execute a function for each row of a set of tables. This is one of the T-SQL  techniques that I never learned early in my career (it wasn’t available), and I haven’t used much outside of looking for execution plans, but it’s a join capability I will certainly look to use in the future.

I’m one of the biggest fans of the APPLY operator out there—my favorite talk is based on it, even.  But in this case, I’m going to say that writing “CROSS APPLY” really didn’t do anything here—times are similar enough that I’d be suspicious that the database engine is doing the same thing both times.

Related Posts

Join Elimination

Lukas Eder has a nice post explaining different forms of automatic join elimination: We intended to fetch all customers and their addresses. But observe: We project only columns from the CUSTOMER table and we don’t have any predicates at all, specifically not predicates using the ADDRESS table. So, we’re completely ignoring any contributions from the ADDRESS table. We never really needed […]

Read More

The Pain Of Multi-Statement TVFs

Andy Mallon walks through a multi-statement table-valued function in Microsoft Dynamics CRM: Look at all those table-valued function calls! Followed immediately by a really expensive hash match. My Spidey Sense started to tingle. What is fn_GetMaxPrivilegeDepthMask, and why is it being called 30 times? I bet this is a problem. When you see “Table-valued function” as an operator […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29