Post reply

Row Count Difference

  • June 16, 2006 at 9:12 am

    #61912

    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

  • June 16, 2006 at 11:17 am

    #644369

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

    Mark

  • June 16, 2006 at 11:27 am

    #644373

    Hi Mark,

    dbcc updateusage didn't resolve the issue.

    Thanks, Brian

    BPH

  • June 16, 2006 at 11:50 am

    #644379

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

    Mark

  • June 16, 2006 at 12:04 pm

    #644383

    Negative.

    BPH

  • June 16, 2006 at 12:11 pm

    #644392

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

    Mark

  • June 16, 2006 at 12:17 pm

    #644397

    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

  • June 16, 2006 at 12:49 pm

    #644404

    Yes, I even disconnected and reconnected. .

    BPH

  • June 16, 2006 at 12:58 pm

    #644407

    Mark,

    rowcount sysindexes says 3583.

    Thanks.

    BPH

  • June 16, 2006 at 1:05 pm

    #644411

    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

  • June 16, 2006 at 1:20 pm

    #644417

    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