How does SQL Server maintain rowcount metadata?
14
votes
2
answers
1296
views
For an example rowstore table...
CREATE TABLE T(Id INT PRIMARY KEY, C1 INT NULL, C2 INT NULL);
There are a variety of different methods of retrieving table row counts from metadata in SQL Server - such as the below
SELECT SUM(rows)
FROM sys.partitions
WHERE object_id = object_id('dbo.T') AND index_id <= 1;
SELECT SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('dbo.T') AND index_id <= 1;
SELECT SUM(rows)
FROM sys.sysindexes
WHERE id = object_id('dbo.T') AND indid <= 1;
SELECT OBJECTPROPERTYEX(object_id('dbo.T'), 'Cardinality')
The execution plans apparently show a variety of different objects being used - such as the below.
* sysrowsets OUTER APPLY OpenRowset(TABLE ALUCOUNT
* sysidxstats CROSS APPLY OpenRowSet(TABLE PARTITIONCOUNTS
* sysidxstats i CROSS APPLY OpenRowSet(TABLE INDEXPROP
What is going on here? Does SQL Server really maintain this metadata in multiple places? If so which is the most reliable method?
Asked by Martin Smith
(87941 rep)
Apr 26, 2025, 09:41 PM
Last activity: Apr 27, 2025, 11:38 AM
Last activity: Apr 27, 2025, 11:38 AM