Walter Meester
HoogkarspelNetherlands
General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2008
>>While the optimizer probably will create identical plans, for me only link fields belong into join-on clause, with a
>>where POH.OrderDate BETWEEN '1-1-2008' AND '12-31-2008'
>
>Really? I've been putting additional conditions on a left join for 20 years now. Its a second nature to me. You can do really beautiful things with it which you cannot achieve as easily in any other way.
Guessing that ease is dependant on the dev. I know a dev always coding the old from [ListofTabler] Where [JoinAdnFilters] just in case he needs the satetment to run on something really ancient.
>
>As for the execution plan. I'm not quite sure about that. If there is an compound index on vendorID, orderdate, TotalDue, the left join will reach full index coverage, as where I suspect the CTE will materialize first. But did not try this as I do not have that database installed.
If there are perf differences due to better usage of compound index - sure, code the fastest way but comment accordingly and tell the DB vendor about it - it might be a weakness in the optimizer/implemetation detail. But coding SQL as a rule to the structure of compound index would need a code review after any compound index change - hard to argue for, unless you can show clear benefits. Not a few would consider such a rule smelly, as it makes the code brittle by introducing artificial tech debt to index changes.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only