SQL Server Row Count

Question

How to count or find out the number of rows a table has without scanning the entire table, possibly using ROW_NUMBER ?

Answer 1

If you need an accurate score, you will need to make COUNT(*) , which scans the clustered index.

You can get an approximate score using the sys.partitions scheme, as shown here http://www.kodyaz.com/articles/sql-rowcount-using-sql-server-system-view-sys-partitions.aspx

Update: to get the counter in 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 2

 SELECT COUNT(*) FROM Table 

will return the number of rows

Answer 3

A bit late for the party here, but in SQL Server 2005 you can 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'm used to using sp_spaceused instead of SELECT COUNT(*) FROM Table , because it is much faster. Most likely, it will not be as accurate as COUNT (*).

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

Answer 4

SQL Server does not have ROW_NUMBER , but just Oracle. Using:

 SELECT COUNT(primary_key) FROM table 

Where primary key column of your table.

Since it is the primary key, it is already indexed, so SQL can calculate it without scanning the entire table (it more accurately uses a clustered index, which is much faster than a full table scan)

You can also use the sys.indexes schema, but its inaccuracy, and you need database access permissions to access, and the user of your application database should not have grants in this schema

Answer 5

I do not believe that you mean this, but try poorly:

 select count(*) from table