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.