Count(*) vs Count(1)

2018-06-23 18:25:48

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?

(The specific database is SQL Server 2005 .)

There is no difference.

Reason:

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

"1" is a non-null expression: so it's the same as COUNT(*) . The optimizer recognizes it for what it is: trivial.

The same as EXISTS (SELECT * ... or 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

Edit, Aug 2011

Similar question on DBA.SE.

Edit, Dec 2011

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

Case:

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

That is, the ANSI standard recognizes it as bleeding obvious what you mean. COUNT(1) has been optimized out by RDBMS vendors because of this superstition. Otherwise it would be evaluated as per 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. If one or more null values are eliminated, then a completion condition is raised: warning-

In SQL Server, these statements yield the same plans.

Contrary to the popular opinion, in Oracle they do too.

SYS_GUID() in Oracle is quite computation intensive function.

In my test database, t_even is a table with 1,000,000 rows

This query:

SELECT  COUNT(SYS_GUID())
FROM    t_even

runs for 48 seconds, since the function needs to evaluate each SYS_GUID() returned to make sure it's not a NULL .

However, this query:

SELECT  COUNT(*)
FROM    (
        SELECT  SYS_GUID()
        FROM    t_even
        )

runs for but 2 seconds, since it doen't even try to evaluate SYS_GUID() (despite * being argument to COUNT(*) )

Clearly, COUNT(*) and COUNT(1) will always return the same result. Therefore, if one were slower than the other it would effectively be due to an optimiser bug. Since both forms are used very frequently in queries, it would make no sense for a DBMS to allow such a bug to remain unfixed. Hence you will find that the performance of both forms is (probably) identical in all major SQL DBMSs.

链接地址: http://www.djcxy.com/p/66620.html

上一篇: fd，monadLib和选择的悖论

下一篇: 计数（*）与计数（1）