April 2, 2010 at 11:42 am
On one table in a SQL 2005 database, I am seeing a difference between the rows count for a the table in SYSINDEXES and the row count return by SELECT COUNT(*) for this table. SELECT COUNT(*) returns a slightly greater number. I have updated statistics on the table and even detached/reattached the database with update statistics. And I rebuilt the indexes on this table. What might cause this?
April 3, 2010 at 10:26 am
The row counts returned are not guaranteed to be exact - in fact they are documented as being 'approximate'. The numbers you see in the compatibility view 'sysindexes' might also be slightly different from the number returned by querying the preferred sys.partitions system view. Try a query like the following:
SELECT [rows]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID(N'Production.TransactionHistory', N'U')
AND P.index_id IN (0, 1);
Chances are that the results will be exactly the same, but quite a lot of the internals changed between 2000 and 2005, so I would always choose to use the newer facility wherever possible. An example of a change is the new ROW_OVERFLOW allocation units in 2005. Another reason to change is that sysindexes is deprecated, and will be removed from a future product version.
There is a note in the documentation for sysindexes to say that the numbers returned might be slightly different from those returned under SQL Server 2000.
Finally, if the database was upgraded from SQL Server 2000, you might like to rebuild the clustered index at some point, and run a data purity check with DBCC CHECKDB. Rebuilding the clustered index will ensure that allocation unit ids are updated to match 2005's rules.
References:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply