Post reply

# of Rows in Enterprise Manager and Query Analyzer

  • July 21, 2004 at 11:51 am

    #85894

    I am working with a SQL Server 2000 database of around 566 tables with the database size of approx. 86 GB. When I am looking at properties of any table in Enterprise manager, I can see the number of rows in specific table.

    Then, when  I go to query analyzer and type the query "Select Count(*) From <Table Name>", I get the number of rows in that table.

    Here I am very much confused because the number or rows in both cases are completely different.

    Any idea why the number of rows differ?

    Thanks

    Nilesh Leuva

  • July 21, 2004 at 12:40 pm

    #515519

    Hi Nilesh,

    Your indexes seem to have got out of whack .... try updating the statistics or rebuilding the indexes. That should help - from EM, the rowcount is selected from the Sysindexes table . This is the value you are seeing in the properties window

     SELECT *

     FROM dbo.sysindexes

     WHERE indid < 2 and id = object_id('dbo.tablename')

    Thanks!

    Aengus

  • July 21, 2004 at 8:14 pm

    #515563

    Yep, rowcounts in EM are an estimation based off of your indexes on those tables which can be inaccurate to say the least.

    Wes

  • July 22, 2004 at 7:45 am

    #515674

    You should be able to use DBCC UPDATEUSAGE WITH COUNT_ROWS to update the row count in the sysindexes table.

     

Viewing 4 posts - 1 through 3 (of 3 total)

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