Microsoft KB Archive/73006

-

The information in this article applies to:


 * Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0, 7.0 Service Pack 1

-

SUMMARY
The following is the query most often used to return the number of rows in a given table:

  SELECT COUNT(*) FROM

For large tables, this query may take a while to run. There is, however, a faster method of determining the number of rows in a table.

MORE INFORMATION
In some applications, you might want to maintain a constant tally of the number of rows in a table. If you use the query shown above, the query expends considerable overhead counting each row of the table.

The &quot;rows&quot; column in the sysindexes table can also return the current number of rows in any given table. Use the following query to retrieve the row count comparatively quickly, since querying the sysindexes table needs to find only one row:

         SELECT rows FROM sysindexes WHERE id = object_id (&quot; &quot;) AND indid < 2