ANOTHER METHOD TO COUNT(*) SQL Server v7.0

To find the total number of rows in a given table, you probably use a

statement similar to SELECT COUNT(*) FROM TableX. If you are looking

for a quick approximation of the rowcount, another method is available

that may be faster.

There is a system table that you can query to get a rowcount for every

table with a defined clustered index. By using the following query, you

can determine the number of rows in the table based upon the value

stored in the sysindexes table.

SELECT rows FROM sysindexes WHERE id =

OBJECT_ID('authors') AND indid < 2

The indid portion of the query identifies the type of index that is

defined upon the table. An indid of 1 is a clustered index; greater

than 1 is a nonclustered index (except for 255, which is an entry for

tables that have text or image data). Obviously, you cannot apply a

WHERE clause to this query based upon some criteria within the target

table. Additionally, this technique does not work with views. If you

were counting only the rows where Fname = 'John', then you would have

to resort to the regular method of SELECT COUNT(*) FROM the actual

table.