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