Get row count for tables quickly in SQL Server

Get row count for tables quickly in SQL Server

RowCountAs 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.

[sourcecode language=”sql”]
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
[/sourcecode]

Once you create “GetRowCount()” table valued function in your database, you can query it like this:

[sourcecode language=”sql”]
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
[/sourcecode]

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)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Get row count for tables quickly in SQL Server”

  1. Pingback: Monica

Comments are closed.