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.

row count

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.

row count of all table using sys.partitions

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.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

1 Response

  1. Laszlo says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *