INF: Fast Query to Return Number of Rows in a Table

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

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

   SELECT COUNT(*)
   FROM <table name>

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 "rows" 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 ("<table name>")
          AND indid < 2

Notes

1. The "AND indid < 2" clause is necessary because the sysindexes table contains one row for each clustered index, one row for each nonclustered index, and one row for each table that has no clustered index. The "indid" column may contain one of the following values:

         0  if the object is a table
         1  if the object is a clustered index
         >1 if the object is a nonclustered index
   

   The "rows" column is updated dynamically only if "indid" is 0 or 1. If "indid" is greater than 1, the "rows" column is updated only when the index is created and when UPDATE STATISTICS is run. Therefore, adding the "AND indid < 2" clause ensures that the value returned by the query is the current number of rows when the query is run.

2. The "rows" column is NOT updated dynamically while rows are being added to the table using BCP (regardless of whether slow or fast BCP is used), but is updated immediately after BCP finishes.