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.

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)

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.

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

  1. Pingback: Monica

  2. Pingback: Row Count Addiction: Get a Good Fix, Fast | SQL On The Side

Comments are closed.