@PaulWhite Your improvements on the top-descending median solution by Farley comes up with a workaround to avoid a Compute Scalar pushdown. You use A complex IIF with GETDATE to avoid it. But appears that you can just OUTER APPLY (VALUES the calculation to prevent the pushdown (note that CROSS APPLY does not work)
I think this is caused by the fact that, while CROSS APPLY (VALUES is normally folded directly inline with the rest of the plan, OUTER seems to cause a Left Join with a Compute ScalarConstant Scan pair, preventing pushdown.
You did remind me of the old Fizz Buzz challenge though
Maybe it was an excuse to use the ODBC scalar function
I really don't remember
Oh and that's the one with the better solution in the comments as well
> Very nice, Robert, thanks. And no, it's rarely impossible to improve my T-SQL: I hope to write about interesting internals and execution plan features, but I know for sure other people write better T-SQL than me :) Anyway, I trust you found rest of the article useful.
That was a long time ago of course. No one writes better T-SQL than me now
CRLF via gitattributes. GitAction that trims the trailing whitespace and replaces tabs with 4 spaces (regardless of the tab length) - I've tested on pushes directly to main branch. Haven't tested on PRs yet and forks
@PaulWhite This isn't the usual difference between CROSS and OUTER, it's more of an internal compiler optimization, so not obvious to most people. Logically the compiler should have been able to infer that the OUTER APPLY (VALUES is also guaranteed to return exactly one row in every case, so it should also have been folded in. But it doesn't do that.
I'd love for something definitive, do you have any knowledge of that part of the optimizer that we can rely on that it will "always" work that way?
@PaulWhite I wasn't telling you off for writing bad SQL, @ErikDarling seems to do a good job of that :-) Was more interested in confirmation that this works how I think it does
