Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count(*) vs count(1) or count(id) Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 12:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 8, 2016 9:33 AM
Points: 101, Visits: 255
Dear Experts,
I want to know what is difference between count(*), count(id) and count(1) base on performance issues.
We have some quires with count(*) that sometimes throws timeout and we have many Transactions on these tables in our other queries.
we guess that if we change it to count(id) or count(1) it will be better.
Is this change effective?
Any information will be helpful.

Faithfully Yours,
Ashkan


Thanks and regards,
Ashkan
Post #1448316
Posted Wednesday, May 1, 2013 12:16 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 11:57 PM
Points: 8,234, Visits: 16,383
Count(*) includes rows with null values whereas count(id) would not include rows with a null id. Unlikely if the id is a primary key though, so it does depend on the column itself to a certain extent.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1448320
Posted Wednesday, May 1, 2013 12:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 29, 2015 11:50 PM
Points: 1,967, Visits: 1,189
http://msdn.microsoft.com/en-us/library/ms175997.aspx

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1448321
Posted Wednesday, May 1, 2013 12:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:49 PM
Points: 34,176, Visits: 18,328
Here's an article on this site discussing the issue: http://www.sqlservercentral.com/articles/Performance+Tuning/adviceoncount/808/






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1448323
Posted Wednesday, May 1, 2013 1:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 25, 2016 4:34 AM
Points: 1,600, Visits: 1,190
Steve Jones - SSC Editor (5/1/2013)
Here's an article on this site discussing the issue: http://www.sqlservercentral.com/articles/Performance+Tuning/adviceoncount/808/


This is a great article, it's amazing what you can forget over time.... :)


There's no kill switch on awesome!
Post #1448330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse