uncoditioned Count(*) vs System tables

Question

A collegue of mine show me than, to count a record of a table, the following view:

CREATE VIEW [SPR].[TablesCount]

AS

SELECT     s.name cSchema,
           t.name cTable,
           sum(p.rows) eRowCount
FROM       sys.tables t
INNER JOIN sys.schemas s
ON         s.schema_id = t.schema_id
INNER JOIN SYS.partitions p
ON         p.object_id = t.object_id
WHERE      p.index_id < 2
GROUP BY   s.name,
           t.name

performs dramatically faster than a regular

select count(*) from table

why this? shouldn't the db engine be optimized to follow the shortest path always? What's the drawback in the system tables view solution?

Answer 1

The value stored in the metadata is not guaranteed to be correct. DBCC UPDATEUSAGE can be used to correct this when it happens (less likely since SQL Server 2000)

Also it is not transactionally consistent. You can read a count value updated by an uncommitted transaction.

And it is possible for these values to be manually updated as well.

CREATE TABLE dbo.YourTable(X INT)

UPDATE STATISTICS dbo.YourTable WITH ROWCOUNT = 1000000

SELECT     sum(p.rows) eRowCount
FROM       sys.partitions p
WHERE      P.object_id =object_id('dbo.YourTable') AND p.index_id < 2