INF Fast Query to Return Number of Rows in a Table

ID Number: Q73006

1.10 1.11 4.20

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>

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 "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.