SQLServerCentral / Discuss Content Posted by Christoffer Hedgate / Article Discussions / Article Discussions by Author / Advice on using COUNT( ) / Latest Posts

RE: Advice on using COUNT( )

neelaya_patnaik — Wed, 15 Apr 2009 02:34:15 GMT

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

RE: Advice on using COUNT( )

Jeff Moden — Thu, 11 May 2006 23:54:00 GMT

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.

RE: Advice on using COUNT( )

Jeff Moden — Thu, 11 May 2006 23:45:00 GMT

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

RE: Advice on using COUNT( )

Bradley Clark — Thu, 11 May 2006 20:31:00 GMT

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?

RE: Advice on using COUNT( )

S Javed — Mon, 14 Feb 2005 12:07:00 GMT

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.

RE: Advice on using COUNT( )

Antares686 — Tue, 24 Sep 2002 04:05:00 GMT

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

RE: Advice on using COUNT( )

Chris Hedgate — Mon, 23 Sep 2002 08:33:00 GMT

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

RE: Advice on using COUNT( )

Greg Larsen — Mon, 23 Sep 2002 08:08:00 GMT

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, DBAIf you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

RE: Advice on using COUNT( )

ScottStonehouse — Mon, 23 Sep 2002 06:42:00 GMT

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

Advice on using COUNT( )

Chris Hedgate — Sun, 22 Sep 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp