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

Advice on using COUNT( ) Expand / Collapse
Author
Message
Posted Sunday, September 22, 2002 12:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp



--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #6965
Posted Monday, September 23, 2002 6:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 19, 2010 8:47 PM
Points: 1, Visits: 2
I usually code this as count(1) rather than count(*). I'm not sure why, I have just been doing it for years. It seems to do the same thing - same number of logical reads in the tests I have run. Any pros or cons to this style?

Scott Stonehouse



Post #42199
Posted Monday, September 23, 2002 8:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
Good article. Has a lot of valuable information about the in's and out's of COUNT.

I've always used code similar to this for finding the row count, provided there is a clustered index.

SELECT ROWS FROM SYSINDEXES WHERE OBJECT_ID('ORDERS') = ID AND INDID < 2

This command only does "2" logical reads, so I'm guessing it performs, slightly better then
select count(*) from orders.



Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples


Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #42200
Posted Monday, September 23, 2002 8:33 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
Thanks for the input guys.

Scott: Yes, they result in the exact same behaviour and execution plan. SQL uses the best index available to it.

Greg: Absolutely, the fastest way of getting the number of rows is querying the sysindexes table about it. It's just that you can't be sure of this number being properly updated at any given time. I'm not 100% sure of this, but I think you can force SQL to update this value to make sure it is correct by running UPDATE STATISTICS. Also, use the column rowcnt instead of rows, rows is only there for backward compatibility.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu




--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Post #42201
Posted Tuesday, September 24, 2002 4:05 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Wednesday, September 17, 2014 7:25 AM
Points: 8,369, Visits: 740
Chris it is DBCC UPDATEUSAGE.

quote:
Specifies that the rows column of sysindexes is updated with the current count of the number of rows in the table or view. This applies only to sysindexes rows that have an indid of 0 or 1. This option can affect performance on large tables and indexed views.


However 2000 seems to be a little less troublesome about this issue. 7 was and to my knowledge even with SP4 is still notorious for incorrect values.

Good article.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Edited by - antares686 on 09/24/2002 04:06:27 AM



Post #42202
Posted Monday, February 14, 2005 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2005 4:25 PM
Points: 4, Visits: 1

Can I introduce a new topic myself in the forum to seek some advice on?

Sorry I have just joined and can not find it how to post a new topic.

Post #161539
Posted Thursday, May 11, 2006 8:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 17, 2006 6:46 PM
Points: 1, Visits: 1
How can i use count in a statement to just find the total count of computers that havent been updated in the past 14 days? I have 21,000 endpoints in the system and i have a existing query that finds all the machines listed by machine name and time updated?
Post #279469
Posted Thursday, May 11, 2006 11:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677

Something like this, I would imagine...

 SELECT (--Finds count of ALL distinct machine names in the table
         SELECT COUNT(DISTINCT MachineName)
           FROM yourtable
        )
      - (--Finds count of distinct machine names updated in last 14 days
         SELECT COUNT(DISTINCT MachineName)
           FROM yourtable
          WHERE TimeUpdated >= GETDATE()-14
        )
        AS MachinesNotUpdatedLast14



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #279486
Posted Thursday, May 11, 2006 11:54 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677

From the main screen, click on [Resources][Discussion Forums].  You will be taken to a screen with different forums.  The T-SQL forum seems to be the most popular but take the time to scan the list to find the most appropriate forum.  Then, click on that forum and you will be taken to that forum.  Near the top of the window is a "button" to start a new thread... click on it and type your question.

Remember to include information about any tables you have (column names, datatype, etc) for the columns required to solve you problem.  Also, include some sample data and what you'd like the output to look like as well as a description of what process you'd like to follow to get to that output. 



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #279488
Posted Wednesday, April 15, 2009 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 28, 2009 4:28 AM
Points: 1, Visits: 4
Hi Chris

Excellent. Thanks a lot for change my concepts about count(*) and count(Column Name). I also belive Count(ColumnName) is working best. But your article changes my concept.

Again thank you very much.


virgo.neelaya@gmail.com
Post #697319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse