Fastest Way to Retrieve Rowcount for a Table – SQL in Sixty Seconds #096

August 7, 2020

Whenever I am helping my clients with their SQL Server Performance Comprehensive Database Performance Health Check, every single time I get this question. What is the fastest way to retrieve rowcount for a table?

Performance issues most of the time arises when there is a suboptimal code or table is huge and it is properly not maintained. Whenever I ask my clients about the rowcount in the table, they usually run the code something very similar to the following code.

SELECT COUNT(*)
FROM TABLE WITH(NOLOCK)

I think it is very dangerous to run the code like above because it will do the complete table scan to find out the count of the row number. Even though there is nolock hint used it still has to do lots of work just get the rowcount.

Today in this video we will see fastest way tot retrieve rowcount for a table.

As you can see it is pretty simple to find the rowcounts if you use two system tables together. Here is the script which I have used in the video.

Script for RowCount

SELECT	SCHEMA_NAME(t.schema_id) SchemaName,
		t.[name] TableName, 
		SUM(p.row_count) TotalRows
FROM sys.tables t
INNER JOIN sys.dm_db_partition_stats p
ON t.object_id = p.object_id
	AND t.type_desc = 'USER_TABLE'
	AND p.index_id IN (0,1)
-- WHERE t.[name] = 'Invoices'
GROUP BY t.schema_id, t.[name]
ORDER BY TotalRows DESC

Of course, the same thing can be done with the help of the SQL Server Management Studio. I will blog about it some times in the future.

Bloopers

If you watch the complete video, at the end of the video, I have included some bloopers. It was my wife who really helped me edit this video and she thought it will be fun to include them. Let me know what you think about them.