How to get number of rows in a table without using count function

How to get number of rows in a table without using count function?

In SQL Server you can get the approximate number of rows in the table using the below sample script.

[sql]
USE AdventureWorks
GO
DECLARE @tablename AS sysname = ‘Production.Product’;

DBCC UPDATEUSAGE (0, @tablename)
WITH NO_INFOMSGS;

SELECT [ROWS]
FROM SYS.indexes AS IND
INNER JOIN
SYS.partitions AS PRT
ON IND.object_id = PRT.object_id
AND IND.index_id = PRT.index_id
WHERE IND.object_id = OBJECT_ID(@tablename)
AND IND.index_id <= 1;
[/sql]

This script uses sys.partitions to fetch the number of rows in the table.
More about sys.partitions can be found here http://msdn.microsoft.com/en-us/library/ms175012(v=sql.105).aspx

Leave a Reply

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