Today's SQL Server tip

ANOTHER METHOD TO COUNT(*) 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.

In prior versions of SQL Server, the indid value was not consistently 
updated by the application. Version 7.0 is supposed to do a better job 
of this, but don't depend on it. If you need an accurate rowcount, use 
the SELECT COUNT(*) FROM TableX method. If you need a quick approximate 
count on a very large table, use the sysindexes information.

You can review MSDN Books Online for more information regarding the 
sysindexes table and the information it can provide.
http://click.techrepublic.com/Click?q=9a-b6TGQTMpSktgsVJ5izRqVlu3ndRR

------------------------------------------