Different ways to count rows from a table

The most reliable and suggested method is to use count(*)

1 Select count(*) from table_name

But other than that you can also use one of the followings

2 select sum(1) from table_name
3 select count(1) from table_name
4 select rows from sysindexes where object_name(id)='table_name' and indid<2
5 exec sp_spaceused 'table_name'

PS

To update rows column of sysindexes table, you need to run this before executing methods 4 and 5

DBCC UPDATEUSAGE ('database_name','table_name') WITH COUNT_ROWS.