Hi, just wondering if any of you guys use Count(1) over Count(*) and if there is a noticeable difference for SQL Server 2005 in performance? Or is this just a legacy habit that has been brought forward from days gone past?
views:
2442answers:
8
A:
I prefer using COUNT (1). There is no point in loading the DB engine with more work if you're not going to need this data.
User
2009-08-03 10:16:20
Is the query plan different between COUNT(1) and COUNT(*)? Is it actually doing extra work?
Colin Mackay
2009-08-03 10:19:33
I prefer using COUNT(*) as it reflects what I mean better, but I still end up writing COUNT(1) as I know there's the possibility it will have better performance.
Ray Hidayat
2009-08-03 10:36:26
By possibility, I mean, if I'm unsure of how the particular database product will choose to optimise the query, I just write COUNT(1) - but not because I prefer it. I would be very surprised though if Microsoft SQL Server did not optimise COUNT(*) and COUNT(1) equivalently.
Ray Hidayat
2009-08-03 10:38:32
Can you justify why the DB engine thinks it's different?
gbn
2009-08-03 10:45:06
In all fairness, there's the _possibility_ that COUNT(*) could be more efficient than COUNT(1), in a particular pathological implementation.
ChrisInEdmonton
2009-08-13 16:08:17
@ChrisInEdmonton, I've been reading asktom.oracle.com lately. COUNT(1) used to be _slower_ because it was taking the time to check the nullness of 1 for every row. At some point the included an internal rewrite of count(1) to count(*) because so many people believed count(1) was faster.
Shannon Severance
2009-09-02 21:48:49
+5
A:
I would expect the optimiser to ensure there is no real difference outside weird edge cases.
As with anything, the only real way to tell is to measure your specific cases.
That said, I've always used COUNT(*).
Richard
2009-08-03 10:17:05
+1 for refs as well, although you might want to add a summary to your actual answer.
Amber
2009-08-03 10:25:01
COUNT(1) is not based on ordinals. It's the same as count(*). 1 is an expression which can never be NULL. 1st link is Sybase, 3rd link is Oracle, question says SQL Server.
gbn
2009-08-03 10:33:11
Actually, this is very wrong: COUNT(1) is a constant expression. Not an ordinal positions like you can use in an order by clause.
gbn
2009-08-03 10:41:07
deleted my wrong answer, i understood it wrong, thanks for correcting.
Amr ElGarhy
2009-08-03 10:46:58
+5
A:
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(*))
Quassnoi
2009-08-03 10:34:38
+24
A:
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 optimiser recognises it for what is 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
gbn
2009-08-03 10:36:52
+4
A:
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 identical in all major SQL DBMSs.
Tony Andrews
2009-08-03 10:45:17
+1
A:
COUNT(*) and COUNT(1) are same in case of result and performance.
Nakul Chaudhary
2009-08-03 13:10:13
+1
A:
SET STATISTICS TIME ON select count(1) from MyTable (nollck) -- table containing 1 million records. SQL Server Execution Times: CPU time = 31 ms, elapsed time = 36 ms. select count(*) from MyTable (nollck) -- table containing 1 million records. SQL Server Execution Times: CPU time = 46 ms, elapsed time = 37 ms.
I've ran this hundreds of times, clearing cache every time.. The results vary from time to time as server load varies, but almost always count(*) has higher cpu time.
Eyal Z.
2010-02-04 02:27:28
I can't reproduce this. `count(*)` and `count(1)` return results within a few ms of each other, even when counting a table with 4.5 million rows, in my SQL 2008 instance.
Jeff Atwood
2010-02-04 06:15:33