SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advice on using COUNT( )


Advice on using COUNT( )

Author
Message
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11323 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/
ScottStonehouse
ScottStonehouse
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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



Greg Larsen
Greg Larsen
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8513 Visits: 290
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
Chris Hedgate
Chris Hedgate
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11323 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/
Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49846 Visits: 803
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



S Javed
S Javed
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.


Bradley Clark
Bradley Clark
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)

Group: General Forum Members
Points: 432205 Visits: 43484

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)SSC Guru (432K reputation)

Group: General Forum Members
Points: 432205 Visits: 43484

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
neelaya_patnaik
neelaya_patnaik
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search