未编程计数（*）与系统表 [英] uncoditioned Count(*) vs System tables

问题描述

我的同事告诉我，计算表的记录，如下视图：

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

比常规

select count(*) from table

为什么？应该不是db引擎被优化以遵循最短路径总是？
系统表视图解决方案有什么缺点？

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?

推荐答案

元数据中存储的值不能保证正确。 DBCC UPDATEUSAGE 可以用来纠正这个时候发生（不太可能，因为SQL Server 2000）

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

这篇关于未编程计数（*）与系统表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！