[Comments] Clean up your queries and simplify logic with CROSS APPLY

[chadbaldwin]

https://chadbaldwin.net/2021/01/07/use-cross-apply-to-clean-up-queries.html

[gonsalu]

Something to note is that this technique doesn't work if the expression includes window functions, as it changes the window to operate in.

[chadbaldwin]

@gonsalu it's funny you mention that, because Adam Machanic has a really cool method he calls the "parallel apply pattern", which you can use to improve parallelism on queries using windowing functions that have a bad plan. Itzik Ben-Gan mentions it in his windowing functions book and credits Adam, but I couldn't find it online. I asked Adam if it was online anywhere and he sent me this video from a PASS presentation he did:

https://youtu.be/_IRWvlSQxS8

I'd watch it while you have the chance before PASS takes down their YouTube channel. Adam presents the pattern at 54:00, but I'd suggest watching the whole thing, tons of great info there.

[gonsalu]

Thanks for sharing, but that's not quite what I was trying to highlight.

For example, let's say you have the following query:

SELECT expr1 = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
     , expr2 = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * -1
  FROM sys.objects

The result set will look like:

expr1    expr2
-----    -----
    1       -1
    2       -2
    3       -3
...

If you try to use CROSS APPLY to have the ROW_NUMBER() window function as a common expression:

SELECT expr1 = X.rn
     , expr2 = X.rn * -1
  FROM sys.objects
 CROSS APPLY (SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))) AS X

Then, the result set will look like the table below, which might not be the expected result.

expr1    expr2
-----    -----
    1       -1
    1       -1
    1       -1
...