SELECT COUNT(*) versus COUNT(PrimaryKeyColumn)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT COUNT(*) versus COUNT(PrimaryKeyColumn)

Author

Topic

ronstone
Starting Member

32 Posts

Posted - 2006-11-16 : 13:46:17

Is there a performance gain/loss between these two, when trying to get the count?

Should one be used over the other?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-16 : 13:53:32

no difference.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-16 : 14:28:43

Actually count(<columnname>) does not count NULL occurrences.
Not a deal if the column is PK since a PK column can't be null.

If the column is nullable, then * is faster. (if it is a rowcount you want)

rockmoose

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 18:30:22

Rockmoose this is probably a silly question but Is it possible that a row can contain all nulls therefore even count (*) wont work either or will it ?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-16 : 18:42:44

count(*) returns the rowcount
count(colname) return the count of all non-null values

It does not matter if all values are null.
But you are not desiging databses with null columns are you?

rockmoose

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-16 : 18:59:51

Only when they want them that way, but like to pratice cloumn constraints better than have a trigger do it :) merci.

Kristen
Test

22859 Posts

Posted - 2006-11-17 : 00:33:26

COUNT(PrimaryKeyColumn) won't work on a database where all columns could be NULL, because, ipso facto, the PK can't be NULL.

My take is that the convention is to use COUNT(*) and there is a possibility that the optimiser is wired to take special action when it detects that syntax. Also, I'm not sure whether counting a Column Name on a table with a multipart PK is always going to be as efficient as whatever the optimiser decides for COUNT(*).

Lastly COUNT(*) tells me that the outcome is a count of all rows in the table. With COUNT(SomeColumn) I have to think about whether that column allows NULLs or not to consider whether it is some special case. And of course if the PK changed (seems unlikely!) I'd have to do some reprogramming; or for that matter I might just make a mistake and wrongly think a column was PK/NOT NULL when in fact it wasn't.

So COUNT(*) me in!

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-17 : 04:21:24

cheers Kristen seeing as we are talking about count() could you answer this for me please which I posted earlier on another topic

Will these two return statements the same value ?

SELECT COUNT(Contract.ContractNo) AS NumContracts FROM Contract

SELECT COUNT(Contract.ContractNo) AS NumContracts
FROM Contract LEFT JOIN Faculty
ON Contract.Supervisor = Faculty.FacNo

Kristen
Test

22859 Posts

Posted - 2006-11-17 : 05:26:42

Not necessarily. If one CONTRACT row can have multiple matching FACULTY records the second count would be bigger.

With a LEFT OUTER JOIN the COUNT cannot be LESS in the second example, whereas with an INNER JOIN it might be (i.e. where Contract.Supervisor had NO matching Faculty.FacNo)

You could do: SELECT COUNT([red]DISTINCT{/red] Contract.ContractNo) if there are multiple matches somewhere in the JOINs, and ContractNo is unique within the Contract table.

Kristen

Kristen

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-11-17 : 06:03:58

Cheers well described thank you Kristen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources