选择“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?

举个例子:

从表中选择一个，其中 c=1 和 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

update 我试图避免执行两个查询或条件语句，我只需要这样的东西:如果c 条件"失败，则有一种方法可以避免执行繁重的d 条件"，因为它是在我的情况下不需要.

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屋！