Rows in sysindexes is not accurate

(
Jan 23, 2013
In Oracle, you use this to figure out the number of records in a table.
select owner,table_name,num_rows from all_tables where table_name=upper('xxx')
)

This is what I learned today.

Rows column in sysindexes is not accurate and thus can't be used to determine number of rows in the associated table. The row_count in the new sys.dm_db_partition_stats is also not reliable. That makes result from sp_spaceused also not accurate since it relies on those system views.


So it comes a conclusion that it should not be used in program for your business need, but it is still good for DBA to use for system maintenance or to quickly get a rough row counts in large table to fulfill on-demand request.

Here is a script you can use in SQL 2005 and above to get a list of index and their row counts on a table.The sum of row_count is the number reported in sp_spaceUsed.

select i.name,s.partition_id, i.type_desc,s.row_count
from sys.indexes i join sys.dm_db_partition_stats s
on i.object_id=s.object_id and i.index_id=s.index_id
where i.object_id=object_id('dbo.tablename')

sysindexes view is to be deprecated so that try not to use it in the work.

In order to get more accurate result,DBCC UPDATEUSAGE WITH COUNT_ROWS can be executed, and sp_spaceused can be used afterword to get row counts at that moment.

Note, exec sp_spaceused @updateusage = ‘true’ equals running DBCC UPDATEUSAGE before running sp_spaceused, it won't update rows used in sysindexes or sys.dm_db_partition_stats.

Why SQL Server cannot guarantee accurate allocation information and row counts? It is said in an article that the reason is to reduce database blocking. If table space allocation and row count information were to be maintained accurately on every INSERT and DELETE, or when an index is dropped, or when a large bulk copy operation is performed, database concurrency could suffer as users in a high transaction environment wait for their transactions to complete as the space information is maintained in real time. I will take it for now.