Why is table row count showing incorrect value in sys.partitions or in DMV sys.dm_db_partition_stats
Last week, I had a requirement to check the row counts of all the tables present in a database. I used attached article to get the row counts of all tables. We get row counts of all tables using system catalog view sys.partitions or DMV sys.dm_db_partition_stats. But row counts were not showing correct for some of the tables. Here i will explain why row counts are not showing equal to the exact row count of the table.
Let’s check the row counts of tables using script that are using sys.partitions system catalog view and compare the output of a table with the output of SELECT COUNT (*) .
Get Row Counts of All Tables using sys.partitions or DMV sys.dm_db_partition_stats
System catalog view sys.partitions contains a row for each partition of all the tables and most types of indexes in the database. All tables and indexes in SQL Server contain at least one partition, whether they are explicitly partitioned. Run below script to get the row count of all tables in a database. If you want to find row counts using DMV sys.dm_db_partition_stats, i would suggest you to visit our attached article how to find row counts of all tables in a database.
--Run below script to get the row counts of all tables in a database.
SELECT SCHEMA_NAME(O.schema_id) + '.' +
O.Name As [Schema.TableName], SUM(P.rows) AS 'Total_RowCount'
FROM sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE O.type = 'U'
GROUP BY O.schema_id, O.Name
You can see row count of all tables in below screenshot.
Now we will check row count of a table using SELECT COUNT(*) command. I ran below command and you can see that row count is not same here. Row count came using sys.partitions system catalog view has more value than the row count of SELECT COUNT (*).
As i said above “sys.partitions or DMV sys.dm_db_partition_stats contains a row for each partition of all the tables and most types of indexes in the database” so when you are fetching row counts of tables using sys.partitions system catalog view or DMV sys.dm_db_partition_stats, it will display the row counts after adding indexes row counts and table row counts. That is why value is showing more in sys.partitions or in sys.dm_db_partition_stats output.
Suppose you have a table with 5000 rows in it and you’ve defined 5 indexes on it, the above query would show 5*5000=25000 rows for that table. So, it isn’t just that some rows are inconsistence, it is that some rows are multiplied by the number of indexes they have.
If we need correct row count using the system cataog view sys.partitions then we need to put a condition in WHERE clause for index_id column. We can also get total row count of all the heap tables or for all the clustered index tables as well by changing the value of index_id mentioned in WHERE clause. Index_id value indicates the ID of the index within the object to which this table belongs.
- 0 = heap
- 1 = clustered index
- 2 or greater = nonclustered index
We can pass index_id value in above script to get the total row count of all tables whether it is heap or clustered index table. If you need total row count of all the tables present in a database, you should put index_id<2 condition in WHERE clause. Run below scripts to get the row count of all heap & clustered index tables.
--Run below script to get the row counts of all tables in a database.
--Change the value of index_id to 0 or 1 to get total row count to heap or cluster index tables.
SELECT SCHEMA_NAME(O.schema_id) + '.' +
O.Name As [Schema.TableName], SUM(P.rows) AS 'Total_RowCount'
FROM sys.objects O
INNER JOIN sys.partitions P ON O.object_id = P.object_id
WHERE O.type = 'U'
AND P.index_id<2
GROUP BY O.schema_id, O.Name
Now you can run above script and compare the output with the SELECT COUNT(*) output. We can see row count for given table is showing same after adding idex_id condition in below screenshot.
I hope you like this article. You can comment us about your questions in below section. Please follow us on our facebook page and on Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
Basically you wrote an article about correcting a wrong query. I thought you were going to go into details as to why DMV’s can show wrong rowcounts even if you use the correct index filtering.