number of rows SQL Server

Question

How to count or know the number of rows a table has without scaning all the table, maybe using ROW_NUMBER?

Answer 1

There is no ROW_NUMBER in SQL Server, just Oracle. Use:

SELECT COUNT(primary_key) FROM table

Where primary key the primary key column of your table.

Since its a primary key, its is already indexed, so SQL can count it without scanning the whole table (it uses a clustered index to be precise, which is much faster than a full table scan)

You could also use sys.indexes schema, but its not accurate, and you would need database admin priviledges to access, and your application database user is not supposed to have grants in that schema

Answer 2

If you need a exact count, you will need to do a COUNT(*) which will scan the clustered index.

You can get a rough count using the sys.partitions schema, as shown here http://www.kodyaz.com/articles/sql-rowcount-using-sql-server-system-view-sys-partitions.aspx

Update: To get the count into a variable:

DECLARE @cnt INT;
SELECT @cnt = SUM(rows)
FROM sys.partitions
WHERE
  index_id IN (0, 1)
  AND object_id = OBJECT_ID('MyDB.dbo.MyTable');
SELECT @cnt;

Answer 3

SELECT COUNT(*) FROM Table

will return the number of rows

Answer 4

A little late to the party here, but in SQL Server 2005 on, you could also use the sp_spaceused stored procedure:

DECLARE @rowCount AS INT
DECLARE @spaceUsed TABLE(
    [Name] varchar(64), 
    [Rows] INT,
    [Reserved] VARCHAR(50),
    [Data] VARCHAR(50),
    [Index_Size] VARCHAR(50),
    [Unused] VARCHAR(50)
)
INSERT INTO @spaceUsed EXEC sp_spaceused 'MyTableName'
SET @rowCount = (SELECT TOP 1 [Rows] FROM @spaceUsed)
SELECT @rowCount AS 'Row Count'

I've gotten into the habit of using sp_spaceused in place of SELECT COUNT(*) FROM Table because it is much faster. It will most likely not be as accurate as COUNT(*), however.

MSDN: http://msdn.microsoft.com/en-us/library/ms188776.aspx