Finding rows count in a table without Select...

  • Comments posted to this topic are about the item Finding rows count in a table without Select...

  • Hi, but are the results correct or approximate?

  • This is an interesting (and much quicker) method but I found the figures returned don't correlate exactly with results from 'select count(*) from <table name>'

    e.g.

    Select count = 14459745; SP_ROWCOUNT = 14459739

    Select count = 1907910; SP_ROWCOUNT = 1907541

    This may not be too important in whole table rowcounts, but do you know why this is?

    Cheers

    John

  • Thats because statistics for the table is not updated

    Fire 'update statistics tablename' and check the results again.

  • OK thanks. Of course that makes it a little less quick if you have to do an update statistics on the table before you can query it :ermm:

  • Well I just ran update statistics and re-ran SP_ROWCOUNT. I still got the same figures, so not only does update stats take longer than select count... it doesn't update the rowcount stats either :exclamationmark:

    Maybe I'll stick with select count...

  • Hello,

    The script is fine but using MS_foreach is undocumented ! :hehe:

    I prefer to use:

    SELECT table_name,table_rows FROM (

    select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC

    from sysobjects so

    join sysindexes si on so.id = si.id and so.type = 'U'

    )F WHERE RC = 1

    ORDER BY table_name

    HTH,

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Hello,

    The script is fine but using MS_foreach is undocumented ! :hehe:

    I prefer to use:

    SELECT table_name,table_rows FROM (

    select so.name as table_name, si.rowcnt as table_rows,ROW_NUMBER() OVER (PARTITION BY SO.NAME ORDER BY si.rowcnt DESC) AS RC

    from sysobjects so

    join sysindexes si on so.id = si.id and so.type = 'U'

    )F WHERE RC = 1

    ORDER BY table_name

    HTH,

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • @Johnc,

    If you need accuarate numbers then go for count(*). If approximate stuff wud do then use sp_spaceused/SP_ROWCOUNT provided in the article.

    Probably your Update stats took a sample scan. Try,

    Update statistics tablename with fullscan.

    In case if you need to understand statistics, please read two excellent articles

    written by the great Gail Shaw 🙂

    http://sqlinthewild.co.za/index.php/2008/11/04/what-are-statistics-and-why-do-we-need-them/

    http://sqlinthewild.co.za/index.php/2008/11/13/identifying-inaccurate-statistics/

  • I prefer to use system objects that aren't marked for removal from a future release of SQL Server, I prefer not to use undocumented stored procs, and I prefer accuracy in my results:

    SELECTQUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id))

    + '.' + QUOTENAME(OBJECT_NAME(ps.object_id)) AS TableName,

    SUM(ps.row_count) AS row_count

    FROM sys.dm_db_partition_stats ps

    WHERE ps.index_id <= 1

    AND OBJECTPROPERTY(ps.object_id, 'IsMSShipped') = 0

    GROUP BY ps.object_id

    ORDER BY TableName

    Chris

  • From my testing of these scripts, the row counts are only accurate if the table has a clustered index.

    Gary

  • I believe the WHERE clause here:

    Where indid < 2

    means that it's on the clustered index. So wouldn't it alway be accurate regardless of statics, since that's the actual physical layout of the rows?

    Let me know if that's not correct.

    Thanks.

    G. Milner

  • Hi, Manish.

    I turned an email I wrote -- based on your post -- to a colleague (ETL designer) into a blog post on my site here, and cited you. Hope that's OK.

    Thanks.

    G. Milner

  • Regarding the clause "Where indid < 2", if you have a clustered index there will be a row in sysindexes with indid = 1. If you do not have a clustered index, there will be a row in sysindexes = 0.

    From my testing, only the tables with a clustered index (indid = 1) are accurate.

    Gary

  • If you need more accurate results, try using DBCC UPDATEUSAGE first. If you haven't done it in awhile, it could take some good bit of time and will take less time the more you use it.

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

Viewing 15 posts - 1 through 15 (of 21 total)

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