Technical Article

A faster way to count rows in a table

,

SQL Server includes the COUNT function for counting a table's rows - however, it can be slow.  Although querying the "rows" column from the sysindexes table is faster, it is not always accurate (e.g., if a bulk load process has recently taken place).  In addition, you would not be able to use this method in conjunction with a GROUP BY...HAVING construct, as you would if you were using the COUNT function.

Here is a better way: it involves adding an extra column to your tables and giving it a default value of 1.  You then use the SUM function on that column to obtain the row count (if you need to exclude rows that have a NULL value for any given column(s), you can add a WHERE clause specifying "WHERE  IS NOT NULL").

In my tests on a table with 9999 rows, a statement using "SELECT SUM(counter) FROM test" took only 7 ms to run (compared to 11 ms when "SELECT COUNT(*)" was used).  And since SUM is an aggregate function, you can use it with GROUP BY...HAVING, just as you would with COUNT.

SET NOCOUNT ON

/*
Creating the test table
*/
CREATE TABLE test
(c1 varchar(100))
GO

/*
Adding the counter column
*/
ALTER TABLE test
ADD counter INT DEFAULT 1
GO

/*
Populating table
*/
DECLARE @i INT
SET @i = 1

WHILE @i < 10000
BEGIN
    INSERT INTO test (c1)
    VALUES ('test')

    SET @i = @i + 1
END

/*
Testing the code
*/ 
 
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT COUNT(*) FROM test
SELECT SUM(counter) FROM test

/*
Observe the differences between the STATISTICS outputs of these two statements
*/

Read 4,297 times
(8 in last 30 days)

Rate

1.33 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

1.33 (3)

You rated this post out of 5. Change rating