count of rows in table is not accurate.

  • I am using below mentioned query to find out the count of rows in table.

    select a.name,indid, b.rowcnt from sysobjects a

    join sys.sysindexes b

    on a.id=b.id

    where a.name='test'

    and INDID IN (0,1,255)

    but the result of above query and below mentioned query are not same.

    select count(1) from test

    Can anyone explain the reason?

    I want to get total number of rows in table without using count function.

  • i'm not sure but see if updating statistics of the table helps...



    Pradeep Singh

  • The count in sysindexes can be wrong and should never be considered anything other than an approximate value

    Since you're on 2005, try using the row count in sys.partitions. It's also not guaranteed to be always 100% accurate but it should be close. If you want a 100% accurate count, you'll have to count the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Does sp_spaceused work for 2005?

  • it works even in 2008 but will show only the disk space used in the table.



    Pradeep Singh

  • Are you sure? http://msdn.microsoft.com/en-us/library/ms188776(SQL.90).aspx... reason I asked was I don't have a 2005 instance to test, but according to BOL it gives number of rows.

    ///edit...

    You have to include object name e.g.

    sp_spaceused 'test';

  • it does provide the number of rows but i'm not sure whether it picks up the results from the statistics or the actual count. If it provides result from the statistics, chances are it wont be accurate all the time.



    Pradeep Singh

  • It comes from neither statistics nor an actual count. A look at the definition of sp_spaceused shows that the row count is calculated from sys.dm_dm_partition_stats.

    It's probably the same as sys.partitions, mostly accurate but not 100% guaranteed to be so.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ps (7/29/2009)


    i'm not sure but see if updating statistics of the table helps...

    Not update statistics. The counts in sysindexes are not statistics based, they come from the storage engine. You need updateusage.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail. 🙂

    Definately i need to google more on this topic 😉



    Pradeep Singh

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

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