sql - Count（*）vs Count（1）-SQL Server(Count(*) vs Count(1) - SQL Server)

Question

Just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference in performance or if this is just a legacy habit that has been brought forward from days gone past?

(只是想知道你们中是否有人使用Count(1)不是Count(*)并且在性能上是否存在明显差异，或者这仅仅是过去的日子中养成的传统习惯？)

(The specific database is SQL Server 2005 .)

(（特定的数据库是SQL Server 2005 。）)

  ask by super9 translate from so

Answer 1

There is no difference.

(没有区别。)

Reason:

(原因：)

Books on-line says " COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) "

(在线书籍说“ COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) ”)

"1" is a non-null expression: so it's the same as COUNT(*) .

(“ 1”是一个非null表达式：因此它与COUNT(*)相同。)

The optimizer recognizes it for what it is: trivial.

(优化器可以识别它是什么：琐碎的。)

The same as EXISTS (SELECT * ... or EXISTS (SELECT 1 ...

(与EXISTS (SELECT * ...或EXISTS (SELECT 1 ...)

Example:

(例：)

SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1),FKID FROM dbo.tab800krows GROUP BY FKID

SELECT COUNT(*) FROM dbo.tab800krows
SELECT COUNT(*),FKID FROM dbo.tab800krows GROUP BY FKID

Same IO, same plan, the works

(相同的IO，相同的计划，作品)

Edit, Aug 2011

(编辑，2011年8月)

Similar question on DBA.SE .

(关于DBA.SE的类似问题 。)

Edit, Dec 2011

(编辑，2011年12月)

COUNT(*) is mentioned specifically in ANSI-92 (look for " Scalar expressions 125 ")

(在ANSI-92中特别提到了COUNT(*) （查找“ Scalar expressions 125 ”）)

Case:

(案件：)

a) If COUNT(*) is specified, then the result is the cardinality of T.

(a）如果指定了COUNT（*），则结果为T的基数。)

That is, the ANSI standard recognizes it as bleeding obvious what you mean.

(就是说，ANSI标准将其识别为明显的含义。)

COUNT(1) has been optimized out by RDBMS vendors because of this superstition.

(由于这种迷信，RDBMS供应商已对COUNT(1)进行了优化。)

Otherwise it would be evaluated as per ANSI

(否则将按照ANSI进行评估)

b) Otherwise, let TX be the single-column table that is the result of applying the <value expression> to each row of T and eliminating null values.

(b）否则，将TX设为单列表，这是对<T的每一行应用<value expression>并消除空值的结果。)

If one or more null values are eliminated, then a completion condition is raised: warning-

(如果消除了一个或多个空值，则会引发完成条件：warning-)