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.
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.
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.
And after you create sql function on your database, you can call sql function udf_TableRowsCount as follows :
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.