Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:50 PM
Points: 6,319, Visits: 13,625
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: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
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, August 13, 2014 5:16 PM
Points: 1,597, Visits: 1,151
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