Alternative, Efficient Way to Get Table’s RowCount in SQL
I found this post about a more efficient way to get the table rowcount from a given table.
SELECT COUNT(*) statements make a full table scans to return the total table’s row count, it can take a lot of time and a lot of “energy” for a large table.
There is another way to determine the total row count in a table by using the sysindexes system table. There is a column (ROWS) in it that contains the total row count for each table in your database.
You can use the syntax below to speed up the query by several times. You can see this by setting statistics on, as shown below.
SET STATISTICS IO ON
GO
SELECT count(*) FROM tbTest
GO
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2
GO
SET STATISTICS IO OFF
GO
10000
Table ‘tbTest’. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.
rows
———–
10000
(1 row(s) affected)
Table ‘sysindexes’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Recent Buzz