Walter Meester
HoogkarspelPays-Bas
Information générale
Forum:
Microsoft SQL Server
Versions des environnements
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.
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement