Doing a “SELECT COUNT(*) FROM [TableName]” can be a long running task.
Thanks to William Durkin for his example on Twitter to count the rows of a table using the system table “sys.partitions”.

To use it in my daily busines, I have wrapped this up in an T-SQL inline function, like this one:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Benjamin Machuletz
-- Create date: 2020-09-07
-- Description: fast rowcount based upon partition stats
-- =============================================
CREATE FUNCTION FastCount
(
	@TableName NVARCHAR(MAX)
)
RETURNS @Counts TABLE
(
	C INT NOT NULL
)
AS
BEGIN
	INSERT INTO @Counts
		SELECT
			CASE
				WHEN SUM(ROWS) >= 0 THEN SUM(ROWS)
				ELSE -1
			END AS C
		FROM sys.PARTITIONS part
  		WHERE index_id IN(0,1)
		AND part.OBJECT_ID = OBJECT_ID(@TableName)
	RETURN
END
GO

This will return the number of rows in a specific table. If the table doesn’t exist, it returns -1.

Über den Autor