Row Count

  • Hi,

    I am trying to get # of rows for a table.

    I used sp_spaceused, through which I get a number which is different compared to number I get when I use SELECT * FROM TABLE.

    Can anyone help me in understanding process going on behind these commands? AND which way is more accurate?

    I know SELECT * is a performance-kill. But this is a small table so,it doesn't matter..

    Thanks

  • sp_spaceused gets it's row count from the partition statistics rather than counting the actual numbers of records. If your statistics are not up to date, the number may be wrong.

  • Ok, it makes sense now.

    Thanks.

    It seems same is true for checking Table properties...When we right click on a table from object explorer.

    the row count is same as the one I get from sp_spaceused.

  • I once wrote a function that returns the number of rows that one or more tables contain. You can find the code here: http://www.sqlserverandxml.com/2008/01/find-row-count-of-one-or-more-tables.html

    .

  • jacob sebastian (10/3/2008)


    I once wrote a function that returns the number of rows that one or more tables contain. You can find the code here: http://www.sqlserverandxml.com/2008/01/find-row-count-of-one-or-more-tables.html%5B/quote%5D

    Other approaches

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx


    Madhivanan

    Failing to plan is Planning to fail

  • sql-oholic (10/2/2008)


    Hi,

    I am trying to get # of rows for a table.

    I used sp_spaceused, through which I get a number which is different compared to number I get when I use SELECT * FROM TABLE.

    Can anyone help me in understanding process going on behind these commands? AND which way is more accurate?

    I know SELECT * is a performance-kill. But this is a small table so,it doesn't matter..

    Thanks

    The reason why sp_spaceused shows a different number is because things have to happen to update the number. To force the update, you can use DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS as Madhivanan's link advises. You can also use any one of the other tricks. The real key is that all of the methods are going to be slow if you don't have at least one proper index which should probably be a clustered index.

    Using sp_space used is the least accurate method.

    --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)

  • Thank you ALL for your time and inputs. It has helped me in understanding the concepts...

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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