COUNT(*) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

COUNT(*)

what is the differene between count(*) and count(1)?
Buth will return the same result except that count(*) will make use of index if any Madhivanan Failing to plan is Planning to fail
count(1) will make use of index too. On Oracle 8 and 9 I think, using count(1) saves compile time because oracle prepares column list when * is used even though it is not needed for count aggregate function. On mssql server I am not sure if it is the case. However, after compilation execution plan should be always the same.
Well. Somewhere I read that its better to use count(*) than count(1) to make use of index when you use exists If exists(Select * from table)
— Madhivanan Failing to plan is Planning to fail
That’s not true, just compare execution plans, you’ll see no difference.
I agree with mmarovic
Hi ya, I think that once upon a time it used to be more efficient to do If exists(Select 1 from table) but that was corrected quite a while ago now Cheers
Twan
quote:Originally posted by Twan Hi ya, I think that once upon a time it used to be more efficient to do If exists(Select 1 from table) but that was corrected quite a while ago now Cheers
Twan
So you mean If exists(Select * from table) is efficient? Madhivanan Failing to plan is Planning to fail
Why not?
And sometimes even COUNT(*)>0 can improve performance over EXISTS() —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Hi, If exists(Select * from table) is usually used to know if the table is empty or not. EXITS function only returns TRUE or NOT TRUE values and it does not list columns, so always uses * internally this is why you get the same performance. Here in this example EXITS returns TRUE in the first row, so it would be faster than
if count(*) > 0 then…
As I’ve mentioned, sometimes it *can* be faster to use COUNT(*)>0, because when using EXISTS() the optimiser expects to find a matching row quickly. If that is the case, using EXISTS() is advantegous over COUNT(*). However, if it usually takes a long time to find a matching row, it might be better to use COUNT(*). So it kind of depends on the assumption if many rows match a condition or not. As this is relatively now to me, I’m doing some research on this in order to write some material here for the website regarding this. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, it would be excellent to see some numbers to compare here. Do you plan to compare NOT EXISTS against LEFT JOIN … WHERE <outercolumn> IS NULL too?
My understanding is that <i><b>select Count(*) … where…</b></i> has to match the same criteria as <i><b>exists(select * …where …)</b></i> because it counts matching rows. So select count(*) has to find all matching rows, while exists(…) has to find one. I’m sure you found something that is exception of the rule, so I’m looking forward for the explanation. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
Recently there were some discussions about that in the private MVP newsgroup. I first have to check what is under NDA and what is public. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Mirko, The thing with EXISTS is that it might take more time than you would expect, to find that one match for the criteria. This is especially true for the NOT EXISTS variation, but also for EXISTS.
I agree about not exists. However, the rows you have to find with exists (one or zero) is a subset of rows you have to find with count (all of them). So if you need a lot of time to find one matching rows you need at least as much to find all matching rows.
I can imagine exception in case QO chooses execution plan that is an shortcat (heuristic) assuming it will find the row among a first few scaned, however I don’t remember seeing such execution plan. Even in that hypotetical scenario, it may be possible to fix execution plan using hints without switching to the solution that should be slower.
Some good info on count:
http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp For example count(*) will include rows with nulls but count(my_nullable_column) will not.
Guys, I need your advise on the performance comparison I’m ready to set up now. Here’s my idea so far:<br /><br />- Table containing +300k rows<br />- Structure doesn’t really matter. However, I thought of having several common columns, like an INT, a DATETIME, a VARCHAR(50). An IDENTITY column for convenience is always included as PRIMARY KEY. Anything else?<br />- Comparison with both clustered index defined and without.<br />- Searching with existance of rows in the "lower third" of the values of the given column to be searched. Likewise the "middle third" and the "upper third". I mean, the likelihood to find a match in a given third will be (much) higher than in the other thirds. Hope you get what I mean here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />- Comparing EXISTS(), COUNT() and LEFT JOIN. <br /><br />What else?<br /><br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
Can you fake outdated column statistics? With 300K rows, perhaps dropping all indexes apart from the PK is a good way to fake heavy loads, bad statistics, etc. Lookiong forward to the results!
Hm, never thought of outdated statistics. Might be doable, when setting autostats off and changing a significant amount of data. [?] —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

I would double number of rows just to mitigate the effect of usual query time fluctuation (is the the right word? [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]). When you are talking about join vs exists it means you want also to test the case:<br /><pre id="code"><font face="courier" size="2" id="code">select …<br />from A<br />where not exists(select * from B where …)</font id="code"></pre id="code">If so consider A is master and B is child and also A is a child but existence criteria is more complex then just A.fk = B.pk. Anyway, for me it would be enough to demonstrate just one case where count(*) is better then exists(…). [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Also I am curous to see how not exists(…) compares against left join … not null after all services packs are applied. I must admit I haven’t tested it for years.
Good point with a second table. Then I suggest we need to have 2 scenarios with that second table:
– only few rows <1000
– about the same number —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

quote:Originally posted by Argyle Some good info on count:
http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp For example count(*) will include rows with nulls but count(my_nullable_column) will not.
Argyle… I tried creating a new table with a nullable column and a non nullable column. when i queried using count(*) and count(nullable_column), both returned same number of rows. so, does this mean that count(nullable_column) will include nulls? Thanks,
Ram
quote:Originally posted by ramkumar.mu
quote:Originally posted by Argyle Some good info on count:
http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp
For example count(*) will include rows with nulls but count(my_nullable_column) will not.
Argyle…
I tried creating a new table with a nullable column and a non nullable column. when i queried using count(*) and count(nullable_column), both returned same number of rows. so, does this mean that count(nullable_column) will include nulls? Thanks,
Ram

Try this example:
declare @table table(col1 int,col2 int)
insert into @table(col1) values(1)
select count(*),count(col2) from @table
quote:Originally posted by ramkumar.mu
I tried creating a new table with a nullable column and a non nullable column. when i queried using count(*) and count(nullable_column), both returned same number of rows. so, does this mean that count(nullable_column) will include nulls?
No, count(*) will include count of all rows, while count(nullable_column) will include count of rows where nullable_column is not null, as you can see from the nice example posted by Ranjit.
]]>