SQL Table Rowcount different from Select Count in SQL Server

Question

I am using Microsoft SQL Server.

I have a Table which had been updated by 80 rows.

If I right click and look at the table properties the rowcount say 10000 but a select Count(id) from TableName indicates 10080.

I checked the statistics and they also have a rowcount of 10080.

Why is there a difference between the Rocount in Properties and the Select Count?

Thanks, S

Answer 1

This information most probably comes from the sysindexes table (see the documentation) and the information in sysindexes isn't guaranteed to be up-to-date. This is a known fact in SQL Server.

Try running DBCC UPDATEUSAGE and check the values again.
Ref: http://msdn.microsoft.com/en-us/library/ms188414.aspx

DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

Example:

DBCC UPDATEUSAGE (0)

Answer 2

Update the statistics. That's the only way RDBMS knows current status of your tables and indexes. This also helps RDBMS to choose correct execution path for optimal performance.

SQL Server 2005

UPDATE STATISTICS dbOwner.yourTableName;

Oracle

UPDATE STATISTICS yourSchema.yourTableName;