SQL RowCount using SQL Server System View sys.partitions
There may be different ways of SQL rowcount in a database table.
But it is very fast to use sql count rows in a table using SQL Server system view (sys.partitions).
This SQL tutorial will demonstrate sql developers how they can find total sql rowcount in a sql table using sys.partitions SQL Server system view.
In the below sql query, if you comment the line where sql table name is given as a criteria in WHERE clause, t-sql developers can get a list of all tables in the sql database and corresponding row count in that sql table.
Please note that the index id 0 is used for Heap, index id 1 is used for clustered index.
And only one of these two sql index types are used in the sum of sql row count.
SELECT
OBJECT_NAME(object_id) AS [Table Name],
SUM(rows) AS [SQL RowCount]
FROM sys.partitions
WHERE
index_id IN (0, 1) -- 0:Heap, 1:Clustered
AND object_id = OBJECT_ID('Sales.Store')
GROUP BY
object_id
This method is an alternative way of SELECT COUNT(*) FROM [Table Name] for most common SQL Server rowcount method.
There is an implementation of sp_MSForEachTable to display row count of all tables here for sql developers.
Free SQL Comparison tools
Trusted by thousands of users
Download your copy now
We can create user defined function, so that tsql developers can use this sql function to return the records count of a specific database table easily.
CREATE FUNCTION udf_TableRowsCount(
@tablename sysname
) RETURNS bigint
AS
BEGIN
DECLARE @count bigint
SELECT
@count = SUM(rows)
FROM sys.partitions
WHERE
index_id IN (0, 1) -- 0:Heap, 1:Clustered
AND object_id = OBJECT_ID(@tablename)
GROUP BY
object_id
RETURN @count
END
GO
And after you create sql function on your database, you can call sql function udf_TableRowsCount as follows :
SELECT [row count] = dbo.udf_TableRowsCount('Sales.Store')
SELECT [row count] = dbo.udf_TableRowsCount('Production.Product')
Please note that for the above t-sql function example, the SQL Server 2008 R2 sample database AdventureWorks is used. You can download sample database following the link.
The output of the above sql function displaying sql rowcount of the two database tables can be seen in the below screenshot.
|