SQL Server 是否优化常量表达式? [英] Does SQL Server optimise constant expressions?

问题描述

如果我在 SQL Server 中做这样的事情...

If I do something like this in SQL Server...

select *
from mytable
where 1=1

...1=1 条件是否得到了优化，还是实际上对每一行都进行了评估?

...does the 1=1 condition get optimised out, or is it actually evaluated for each row?

我也有兴趣了解其他 DBMS 在这方面的表现，尤其是 MySQL.

I'd also be interested in knowing how other DBMSes behave in this regard, particularly MySQL.

推荐答案

SQL Server:

第一个例子:

1=1 谓词被 SQL Server 查询优化器自动删除，因为它总是 true:

1=1 predicate is automatically removed by SQL Server Query Optimizer because it's always true:

第二个例子:

因为 1=2 谓词总是 false，所以执行计划不包括数据访问操作符 (Table/Index Scan/Seek) 从 dbo.Customer 表中读取数据.相反，执行计划包括一个 Constant Scan 运算符，它将返回 dbo.Customer 表中没有任何行的列列表.这是矛盾检测 :-)

Because 1=2 predicate is always false, the execution plan don't includes a data access operator (Table / Index Scan / Seek) to read data from dbo.Customer table. Instead, the execution plan includes an Constant Scan operator which will return the list of columns from dbo.Customer table without any row. This is contradiction detection in action :-)

注意:我使用的是 SQL Server 2012 Developer Edition.

Note: I used SQL Server 2012 Developer Edition.

这篇关于SQL Server 是否优化常量表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！