Row Count Difference

  • Good morning,

    SQL 2000 SP4, Windows2003

    Strange issue appeared this morning that I haven't encountered before.

    Table1 has 3591 rows in it when I run 'select count(*) from table1'.

    When I return the entire results set in qa, I scroll down to row 3591.

    BUT, When I use SQL Enterprise Manager, right click on table1, select properties and look at the row count, I see 3583.

    Ran dbcc checkdb, dbcc newalloc, sp_updatestats, and dbcc checkcatalog.

    Not sure why this happens.

    Have a nice day.

    BPH

     

    BPH

  • Run a DBCC UPDATEUSAGE on that table and then do your record count using both methods.  Let us know what happens.

    Mark

  • Hi Mark,

    dbcc updateusage didn't resolve the issue.

    Thanks, Brian

    BPH

  • Next thing to try is a DBCC DBREINDEX on that table and see what happens.

    Mark

  • Negative.

    BPH

  • What does the "rowcnt" column in sysindexes show for that table?

    Mark

  • One last thing, when you ran the UPDATEUSAGE command, did you do a refresh on the table in EM before looking at the properties?

    Mark

  • Yes, I even disconnected and reconnected. .

    BPH

  • Mark,

    rowcount sysindexes says 3583.

    Thanks.

    BPH

  • From what I know, the GUI uses sysindexes to report the row count of a table, that's why you are getting a different number.  However, what is the mystery to me is why the DBCC UPDATEUSAGE isn't changing the rowcnt column to the number of actual rows in the table.  Did you try using the WITH COUNT_ROWS option in the UPDATEUSAGE statement.  From BOL:

    COUNT_ROWS

    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.

    Mark

  • Mark,

    Awesome job!! With Count_Rows did the trick.

    Thanks for all your help. enjoy the weekend.

    Brian

    BPH

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

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