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'
6 DBCC CHECKTABLE('table_name')
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.