Get row count for tables quickly in SQL Server
As a DBA, This is my prime responsibility to keep track of table size and number of rows it has. If I notice any unusual increase or decrease in size or number of rows in table, I must have to investigate this.
I have seen people used to execute COUNT() aggregate function on table to know the total number of rows in table. This operation may affect performance of server if table is having billions of row.
I don’t recommend COUNT() aggregate function on table to know total number of rows in table. I recommend to use sys.partition and sys.allocation_units catalog view along with sys.tables and sys.schemas catalog view.
I have created one user defined table valued function which do this job for me. It is much faster then COUNT() aggregate for sure.
CREATE FUNCTION GetRowCount() RETURNS TABLE RETURN SELECT sch.name AS SchemaName, SysTab.Name AS TableName, sch.name + '.' + SysTab.name AS QualifiedName, SUM( CASE WHEN (Parti.index_id < 2) AND (alloUni.type = 1) THEN Parti.rows ELSE 0 END ) AS Rows FROM sys.partitions AS Parti INNER JOIN sys.allocation_units AS alloUni ON Parti.partition_id = alloUni.container_id INNER JOIN sys.tables AS SysTab ON SysTab.object_id = Parti.Object_ID INNER JOIN sys.schemas AS sch ON sch.schema_id = SysTab.schema_id GROUP BY SysTab.name, sch.name GO
Once you create “GetRowCount()” table valued function in your database, you can query it like this:
SELECT * FROM GetRowCount() --you can even filter this function --to get value for specific table for eg: --WHERE TableName='SalesOrderDetail' ORDER BY Rows DESC GO
This is one of fastest approach to know the total number of rows in table. I have already written two articles related to table size, if you are interested, have a look:
- Calculate Table & Schema size in SQL Server (Click Here)
- List of Table in Filegroup with table size in SQL Server (Click Here)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.