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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.





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.

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems