Advice on using COUNT( )

Question

Advice on using COUNT( )

Chris Hedgate

One Orange Chip

Points: 25041
More actions
September 22, 2002 at 12:00 am

#158909

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/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ScottStonehouse Grasshopper Points: 19 More actions · Answer 1

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 SSC-Insane Points: 20961 More actions · Answer 2

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 One Orange Chip Points: 25041 More actions · Answer 3

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 SSC Guru Points: 125444 More actions · Answer 4

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 Grasshopper Points: 12 More actions · Answer 5

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 Grasshopper Points: 21 More actions · Answer 6

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 SSC Guru Points: 1004321 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

neelaya_patnaik Grasshopper Points: 17 More actions · Answer 9

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