Is the rows figure stored in sysindexes accurate - SimonS Blog on SQL Server Stuff

Is the rows figure stored in sysindexes accurate

It wasn't guaranteed to be in SQL 2000 and earlier, but work was done in SQL 2005 that should mean that the rows figure stored in sys.partitions should be accurate.

Madhivanan has posted about the different ways to count rows and this is the quickest as it only reads one row rather than having to scan an index http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx

Remember sysindexes in SQL 2000 is now a compatibility view in SQL2005. You should be accessing the system table sys.partitions.

If you have any situations where the row count is not accurate let me know and I will pass on the details to Microsoft.

One option Madhivanan didn't say was

dbcc show_statistics (<table>,<PK>)


-
Published 06 November 2007 21:50 by simonsabin
Filed under: , ,

Comments

07 November 2007 12:53 by Madhivanan

# re: Is the rows figure stored in sysindexes accurate

Thanks Simon.

But after running DBCC Updateusage,

dbcc show_statistics (<table>,<Idcol>) doesnt seem to give correct row count while all other methods give Smile

07 November 2007 13:14 by simonsabin

# re: Is the rows figure stored in sysindexes accurate

I guess you need to issue a UPDATE Statistics statement.

I am guessing that the data returned by DBCC Show Statistics is from the statistics page and not from sys.partitions.

The rowcount on the statistics page will be as of the time the statistics where updated. Due to all the other information on the page about density etc this does make sense.