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?
See Question&Answers more detail:os