How to find the number of records in a table without using COUNT() function?

It is possible by using MAX value of ROW_NUMBER() in a table and from Rows column of sysindexes.
—————————————————————————————
USE [ADVENTUREWORKS2012]
GO
—————————————————————————————
— Finding Row Count using Max function. -– Returns 19614
—————————————————————————————
SELECT MAX(Rnum) AS [Row_Count] FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [AddressID]) AS [Rnum]
FROM [Person].[Address]
) AS [A]
—————————————————————————————
— Finding Row Count using Sysindexes. –- Returns 19614
—————————————————————————————
SELECT ROWS AS [Row_Count]
FROM [sysindexes]
WHERE [id] = OBJECT_ID(‘[Person].[Address]’) AND [indid] < 2
————————————————————————————— 

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *