SQL Server includes the COUNT function for counting a table's rows - however, it can be slow.  Although querying the "rows" column from the sysindexes table is faster, it is not always accurate (e.g., if a bulk load process has recently taken place).  In addition, you would not be able to use this method in conjunction with a GROUP BY...HAVING construct, as you would if you were using the COUNT function.

Here is a better way: it involves adding an extra column to your tables and giving it a default value of 1.  You then use the SUM function on that column to obtain the row count (if you need to exclude rows that have a NULL value for any given column(s), you can add a WHERE clause specifying "WHERE  IS NOT NULL").

In my tests on a table with 9999 rows, a statement using "SELECT SUM(counter) FROM test" took only 7 ms to run (compared to 11 ms when "SELECT COUNT(*)" was used).  And since SUM is an aggregate function, you can use it with GROUP BY...HAVING, just as you would with COUNT.