SQL Server Performance Forum – Threads Archive
Order of WHERE condition
Hello! I have a question. Onhttp://www.sql-server-performance.com/transact_sql_where.asp it is stated that: "If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. This assumes that no parenthesis have been used to change the order of execution. Because of this, you may want to consider one of the following when using AND: Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.If both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.
You may want to consider using Query Analyzer to look at the execution plans of your queries to see which is best for your situation. [6.5, 7.0, 2000] Updated 6-21-2004" From my perspective this seems to be wrong, but I’m not sure I understand it correctly. I would say that the query optimizer decides the order of evaluation of expressions based on whether there are, for example, a column in the expression that is covered by an index, which table is the smallest, et cetera. Is there a source (available on the Internet?) that verifies this or can someone explain a little more what the above really means, in case I have misunderstood it?
With best regards,
Henrik Svensson
I am not aware of a specific URL for this, but these might also help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;820209 Anyway, the SQL standards do not govern this. So it is "implementation-defined". That way MS is free to choose whatever they believe offers the best performance as long as the result is the same. SQL Server will short-circuit when it is known that the result will either be true or false. And SQL Server *will* evaluate in left to right order, but *not* necessarily the left to right order as it is written in the query. The left to right order will be based on the internal query tree. And you can’t tell from a query what this query tree will look like. In other words, you can’t rely on any behaviour to be constant. So, from a programming perspective I would say, the text you’ve cited is a good programming style, but you can’t tell for sure, if it has any significant influence on the decision the query optimizer makes. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
]]>