SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

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.

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
Code

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')
Code

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.

sql-rowcount-using-sql-server-system-view-sys.partitions



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.