Advice on using COUNT( )

  • Chris Hedgate

    One Orange Chip

    Points: 25041

  • ScottStonehouse

    Grasshopper

    Points: 19

    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: 20685

    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

    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

  • Antares686

    SSC Guru

    Points: 125444

    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

    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

    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: 996655

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996655

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • neelaya_patnaik

    Grasshopper

    Points: 17

    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

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

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