SQL Server：选择字面值比选择字段更快吗？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

Subquery using Exists 1 or Exists * 6 answers

子查询使用Exists 1或Exists * 6答案

I've seen some people use EXISTS (SELECT 1 FROM ...) rather than EXISTS (SELECT id FROM ...) as an optimization--rather than looking up and returning a value, SQL Server can simply return the literal it was given.

我见过有些人使用EXISTS(SELECT 1 FROM ...)而不是EXISTS(SELECT id FROM ...)作为优化 - 而不是查找并返回一个值,SQL Server可以简单地返回它的文字给出。

Is SELECT(1) always faster? Would Selecting a value from the table require work that Selecting a literal would avoid?

SELECT(1)总是更快吗?从表中选择一个值是否需要选择文字会避免的工作?

8 个解决方案

#1

For google's sake, I'll update this question with the same answer as this one (Subquery using Exists 1 or Exists *) since (currently) an incorrect answer is marked as accepted. Note the SQL standard actually says that EXISTS via * is identical to a constant.

为了谷歌的缘故,我将使用与此相同的答案更新此问题(子查询使用存在1或存在*),因为(当前)不正确的答案被标记为已接受。注意,SQL标准实际上说通过*的EXISTS与常量相同。

No. This has been covered a bazillion times. SQL Server is smart and knows it is being used for an EXISTS, and returns NO DATA to the system.

不,这已经被覆盖了无数次。 SQL Server是智能的,并且知道它正用于EXISTS,并将NO DATA返回给系统。

Quoth Microsoft: http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4

Quoth Microsoft:http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud = 4

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

EXISTS引入的子查询的选择列表几乎总是由星号(*)组成。没有理由列出列名,因为您只是测试是否存在满足子查询中指定条件的行。

Also, don't believe me? Try running the following:

还有,不相信我?尝试运行以下内容:

SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )

If it was actually doing something with the SELECT list, it would throw a div by zero error. It doesn't.

如果它实际上正在使用SELECT列表执行某些操作,则会抛出一个div为零的错误。它没有。

EDIT: Note, the SQL Standard actually talks about this.

编辑:注意,SQL标准实际上谈到了这一点。

ANSI SQL 1992 Standard, pg 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

ANSI SQL 1992标准,第191页http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

     3) Case:

        a) If the <select list> "*" is simply contained in a <subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>.

#2

In SQL Server, it does not make a difference whether you use SELECT 1 or SELECT * within EXISTS. You are not actually returning the contents of the rows, but that rather the set determined by the WHERE clause is not-empty. Try running the query side-by-side with SET STATISTICS IO ON and you can prove that the approaches are equivalent. Personally I prefer SELECT * within EXISTS.

在SQL Server中,无论在EXISTS中使用SELECT 1还是SELECT *,它都没有区别。您实际上并没有返回行的内容,而是由WHERE子句确定的集合不是空的。尝试使用SET STATISTICS IO ON并排运行查询,您可以证明这些方法是等效的。我个人更喜欢EXISTS中的SELECT *。

#3

When you use SELECT 1, you clearly show (to whoever is reading your code later) that you are testing whether the record exists. Even if there is no performance gain (which is to be discussed), there is gain in code readability and maintainability.

当您使用SELECT 1时,您清楚地显示(对于以后正在阅读您的代码的人)您正在测试该记录是否存在。即使没有性能提升(将要讨论),代码可读性和可维护性也会得到提高。

#4

Yes, because when you select a literal it does not need to read from disk (or even from cache).

是的,因为当您选择文字时,它不需要从磁盘(甚至从缓存)读取。

#5

doesn't matter what you select in an exists clause. most people do select *, then sql server automatically picks the best index

在exists子句中选择的内容无关紧要。大多数人都选择*,然后sql server自动选择最佳索引

#6

As someone pointed out sql server ignores the column selection list in EXISTS so it doesn't matter. I personally tend to use "SELECT null ..." to indicate that the value is not used at all.

有人指出sql server会忽略EXISTS中的列选择列表,所以没关系。我个人倾向于使用“SELECT null ...”来表示根本没有使用该值。

#7

If you look at the execution plan for

如果你看一下执行计划

select COUNT(1) from master..spt_values

and look at the stream aggregate you will see that it calculates

并查看流聚合,您将看到它计算

Scalar Operator(Count(*))

So the 1 actually gets converted to *

所以1实际上转换为*

However I have read somewhere in the "Inside SQL Server" series of books that * might incur a very slight overhead for checking column permissions. Unfortunately the book didn't go into any more detail than that as I recall.

但是,我在“Inside SQL Server”系列丛书中的某处读过*可能会产生非常小的开销来检查列权限。不幸的是,正如我记得的那本书没有详细介绍。

#8

-1

Select 1 should be better to use in your example. Select * gets all the meta-data assoicated with the objects before runtime which adss overhead during the compliation of the query. Though you may not see differences when running both types of queries in your execution plan.

选择1应该更好地在您的示例中使用。 Select *获取在运行时之前与对象相关联的所有元数据,这些元数据在编译查询期间会增加开销。虽然在执行计划中运行这两种类型的查询时可能看不到差异。

#1