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.
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.
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.
In the video, there are two tiny learnings. 1) Getting the name of the schema from schema id and 2) Using the column alias in the order by.
Please leave a comment and give suggestions, what would you like to see more in this SQL in the Sixty Seconds series.
Reference: Pinal Dave (http://blog.SQLAuthority.com)