Post reply

Record Counts not matching

  • October 25, 2005 at 11:20 am

    #102884

    HI. When I go into Enterprise manager and double click on a table and view the table properties, I see the number of rows in that table. Ex. table1 has 3917 rows. NOW, when i do a select on all records OR a select Count(*) on this same table1 i get a count of 9300 rows. Can someone explain how this can be? Very confused.

    Juanita 

     

     

  • October 25, 2005 at 11:25 am

    #599880

    Do not trust any rowcount other than a Select Count(*) From table as most are based on the table statistics for performance of reporting. If your statistics are stale well you are seeing the rest!

  • October 25, 2005 at 11:28 am

    #599882

    you can run sp_updatestats and then dbcc updateusage (not sure of the 2nd one)

  • October 25, 2005 at 12:47 pm

    #599909

    Thanks! i always thought the row count on the table properties were accurate.

    So if I run sp_updatestats, would that show an accurate row count on the table properties?

    Thanks,

    Juanita

     

  • October 25, 2005 at 12:49 pm

    #599911

    What are you trying to accomplish? Depending on the type of activity on a table the statistics can become stale in seconds. The ONLY accurate wat to get a rowcount is Select Count(*) From dbo.table!

  • October 25, 2005 at 12:54 pm

    #599914

    I wasn't trying to accomplish anything in particular. I just happen to view the rowcount on a table and then I did a select and saw the difference in rows. That's when i started to question what was happening. But you all just opened my eyes to a little bit more about sql server. Now i know the best way to get a row count of a table.

    Thanks to all !!

    Juanita

  • October 26, 2005 at 3:02 am

    #600051

    When you click on table you see rough result:

    EM do this so:

    SELECT

    SO.name

    ,SI.rowcnt

    FROM

    sysobjects SO

    JOIN sysindexes SI ON SO.id = SI.id AND SI.indid <2

    WHERE

    SO.xtype = 'U'

    It's good for administration purposes, not for buisness logic programming.

    And think about such thing:

    There is no such thing as COUNT(*) without identifiing lock-level.

    SELECT COUNT(*) FROM Table(NOLOCK)

    SELECT COUNT(*) FROM Table(READPAST)

    SELECT COUNT(*) FROM Table(READCOMMITTED)

    Results may vary.

    Cheers!

  • October 26, 2005 at 4:32 am

    #600069

    Agree with Alexander's post on this, although I would like to point something out from an earlier post that was not quite right.

    SQL Server keeps the row counts updated in a fairly lazy way. It can get quite out of step with reality, esp on large tables. If for some reason you need to fix that (count(*) is still the only way to know for sure) have a look at this:

    dbcc updateusage (db_name, tablename, index_id) WITH COUNT_ROWS

    sp_updatestats will update the stats blob, and will not, as far as I can see, update this rowcnt field on sysindexes.

     

    Mark

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

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