SQL 选择“where子句”评估顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/484135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select "where clause" evaluation order
提问by t3mujin
In Sql Server 2005 when I have multiple parameters do I have the guarantee that the evaluation order will alwaysbe from left to right?
在 Sql Server 2005 中，当我有多个参数时，是否可以保证评估顺序始终是从左到右？
Using an example:
使用示例：
select a from table where c=1 and d=2
select a from table where c=1 and d=2
In this query if the "c=1" condition fails the "d=2" condition will never be evaluated?
在此查询中，如果“c=1”条件失败，则永远不会评估“d=2”条件？
PS- "c" is an integer indexed column, d is a large varchar and non indexable column that requires a full table scan
PS-“c”是一个整数索引列，d是一个大的varchar和不可索引的列，需要全表扫描
updateI was trying to avoid performing two queries or conditional statements, I just need something like: if "c condition" fails there's a way to avoid performing the heavy "d condition", since it's not needed in my case.
更新我试图避免执行两个查询或条件语句，我只需要类似的东西：如果“c 条件”失败，则有一种方法可以避免执行繁重的“d 条件”，因为在我的情况下不需要它。
回答by kdgregory
There are no guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query, using available information.
评估顺序没有保证。优化器将尝试使用可用信息找到执行查询的最有效方式。
In your case, since c is indexed and d isn't, the optimizer should look into the index to find all rows that match the predicate on c, then retrieve those rows from the table data to evaluate the predicate on d.
在您的情况下，由于 c 已编入索引而 d 未编入索引，优化器应查看索引以查找与 c 上的谓词匹配的所有行，然后从表数据中检索这些行以评估 d 上的谓词。
However, if it determines that the index on c isn't very selective (although not in your example, a gender column is rarely usefully indexed), it may decide to do the table scan anyway.
但是，如果它确定 c 上的索引不是很有选择性（尽管不是在您的示例中，性别列很少有用索引），则它可能决定无论如何都要进行表扫描。
To determine execution order, you should get an explain plan for your query. However, realize that that plan may change depending on what the optimizer thinks is the best query right now.
要确定执行顺序，您应该获得查询的解释计划。但是，要意识到该计划可能会根据优化器目前认为的最佳查询而改变。
回答by Orion Adrian
SQL Server will generate an optimized plan for each statement it executes. You don't have to order your where clause to get that benefit. The only garuntee you have is that it will run statements in order so:
SQL Server 将为它执行的每个语句生成一个优化计划。您不必订购 where 子句即可获得该好处。您唯一的保证是它将按顺序运行语句，因此：
SELECT A FROM B WHERE C
SELECT D FROM E WHERE F
will run the first line before the second.
将在第二行之前运行第一行。
回答by Mark Canlas
You can look at the execution plan of the query and determine what it's actually trying to do. I think the query engine of SQL Server is supposed to be doing this type of scanning and will intelligently translate it into operations. Like, if you do "expensive-op AND false", it will quickly evaluate to false.
您可以查看查询的执行计划并确定它实际尝试执行的操作。我认为 SQL Server 的查询引擎应该做这种类型的扫描，并会智能地将其转换为操作。就像，如果您执行“昂贵的操作和错误”，它会很快评估为错误。
From what I've learned, what you type is (and can be) different from what's actually executed. You're merely telling the server what type of results you expect. How it gets the answer does not correlate left-to-right of the code you provide.
据我所知，您键入的内容（并且可能）与实际执行的内容不同。您只是告诉服务器您期望什么类型的结果。它如何获得答案与您提供的代码的从左到右无关。
回答by cgreeno
If you want to be sure you can check the Query Execution Plan. The Execution Plan that MSSQL builds/optimizes is smart enough to check the indexed column before a varchar column.
如果你想确定你可以检查Query Execution Plan。MSSQL 构建/优化的执行计划足够智能，可以在 varchar 列之前检查索引列。
回答by Nitin Midha
Short-Circuit is done when the condition we are referencing only includes literals or constants. So for example lets say we have a table TableA which has column num with all positive numbers from 1 to 10 and then if i write this query.
当我们引用的条件仅包含文字或常量时，短路就完成了。例如，假设我们有一个表 TableA，它的列 num 包含从 1 到 10 的所有正数，然后如果我编写此查询。
Select num from TableA WHERE TableA.num < 0 AND 1/0 = 10.
从 TableA 中选择 num，其中 TableA.num < 0 AND 1/0 = 10。
It will result in error.
它会导致错误。
Is compiler smart enough to determine that my second clause is consists of constants so it should evaluate that before evaluating clause which requires any scan from table or index?
编译器是否足够聪明来确定我的第二个子句由常量组成，因此它应该在评估需要从表或索引进行任何扫描的子句之前评估它？
回答by Robert C. Barth
The MS SQL Server query optimizer does short circuit, yes. Guaranteed.
MS SQL Server 查询优化器确实短路，是的。保证。
Run this:
运行这个：
select 1 where 1 = 0 and 1 / 0 = 10
It will run just fine and not error even though you're dividing by zero because the query optimizer will short-circuit evaluate the where clause. This has implications for any where clause where you're "and"-ing and one of the and parts is a constant.
即使您除以零，它也会运行得很好并且不会出错，因为查询优化器将对 where 子句进行短路评估。这对您使用“and”-ing 并且其中一个和部分是常量的任何where 子句都有影响。
回答by Ric Tokyo
One way to control the evaluation order is with the CASE expression.
控制求值顺序的一种方法是使用 CASE 表达式。
[Edit]
[编辑]
The popular opinion I was trying to express was:
我试图表达的流行观点是：
You cannot depend on expression evaluation order for things like “WHERE OR “, since the optimizer might choose a plan that evaluates the second predicate before the first one. But order of evaluation of the expressions in a CASE statement is fixed, so you can depend on deterministic short circuit evaluation of a CASE statement.
对于诸如“WHERE OR”之类的内容，您不能依赖表达式求值顺序，因为优化器可能会选择在第一个谓词之前求值第二个谓词的计划。但是 CASE 语句中表达式的求值顺序是固定的，因此您可以依赖 CASE 语句的确定性短路求值。
It does get a bit more complicated than that as explained in the site below:
它确实比下面的站点中解释的要复杂一些：
