The easiest way to count the number of rows in a table, from the user perspective, is to use the simple COUNT(*) operator.  A more efficient method to count the number of records in a table is to use SQL Server’s system tables.

Let’s start out with the naive approach and see how it performs.

USE AdventureWorks;
GO

SET STATISTICS TIME ON

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SELECT    COUNT(*)
FROM    Sales.SalesOrderDetail;

The SET STATISTICS will provide us with some simple run time performance metrics.

The two DBCC commands ensure us that we are clearing the query plan and data from cache, so we know we’re getting worst case performance results each time, as if it is the first time the query is executed.

When we run this query we learn that there are 121,317 rows.  Here’s the runtime on my tired old laptop:

SQL Server Execution Times:

  CPU time = 40 ms,  elapsed time = 199 ms.

We also learn that the query has to do a non-clustered index scan on all 121k+ rows in the table.  The estimated total subtree cost of this query is 0.376 (not shown in the image, you would need to hover the sprite over the SELECT operator).

Now let’s look at the system table approach to counting the number of rows in a table:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SELECT    OBJECT_NAME(id),
        rowcnt
FROM        sysindexes
WHERE    OBJECT_NAME(id) = 'SalesOrderDetail'
AND        indid = 1;

This query returns the same 121,317 row count but the runtime is less than 1ms:

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Looking at the execution plan this query does a CLUSTERED index scan against the 125 row sysindex table:  The estimated total subtree cost for this comparable query is .0075 which is just 2 percent of the 0.376 required for the naive approach.

Now here’s where we really make this exciting (OK, a little overstated)…

When loading a data warehouse it’s often nice to be able to monitor the progress of a table load.  Unfortunately, while the table is being loaded the entire table is probably locked up.  So if you attempt to do a COUNT(*) on the table the query just sits there.

If you use the sysindexes approach you’re not even reading from the table that’s being loaded, and your row count is returned almost immediately.

Now let’s stretch out the benefit of this simple system table query one more time.  Let’s use it to return the row count of every table in the database:

SELECT    OBJECT_NAME(id),
        rowcnt
FROM        sysindexes
WHERE    indid = 1
ORDER BY 2 DESC;

This little query returns the row count for all 125 tables in less than 1 ms.  Try doing that with a singe query (ie., not looped or cursor driven… ugh!!!) with the COUNT operator!

The total subtree cost for this query for 125 tables is .02076; much less than the .376 to run the naive query for just 1 table.

I captured the pic of the total estimated subtree cost below:

I hope you are convinced, the system table approach requires a bit more typing but is MUCH more efficient to count rows in a table, particularly if the server is not idling away.

Happy SQL-ng!