选择"where子句".评估单 [英] Select "where clause" evaluation order

问题描述

在Sql Server 2005中，当我有多个参数时，是否可以保证评估顺序将始终从左到右?

In Sql Server 2005 when I have multiple parameters do I have the guarantee that the evaluation order will always be from left to right?

使用示例:

select a from table where c=1 and d=2

在此查询中，如果"c = 1"条件失败，则"d = 2"条件将永远不会被评估?

In this query if the "c=1" condition fails the "d=2" condition will never be evaluated?

PS-"c"是整数索引列，d是大型varchar且不可索引的列，需要全表扫描

PS- "c" is an integer indexed column, d is a large varchar and non indexable column that requires a full table scan

更新我试图避免执行两个查询或条件语句，我只需要类似以下内容:如果"c condition"失败，则有一种方法可以避免执行繁重的"d condition"，因为就我而言，是不需要的.

update I 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.

推荐答案

不保证评估顺序.优化程序将尝试使用可用信息来找到执行查询的最有效方法.

There are no guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query, using available information.

在您的情况下，由于c被索引而d没有被索引，因此优化器应调查索引以查找与c上的谓词匹配的所有行，然后从表数据中检索这些行以评估d上的谓词.

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上的索引不是选择性很强(尽管在您的示例中不是这样，很少对性别列进行索引)，它可能仍决定进行表扫描.

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.

要确定执行顺序，您应该为查询获得一个解释计划.但是，请注意，该计划可能会根据优化器当前认为最佳的查询而改变.

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.

这篇关于选择"where子句".评估单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！