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, so you need to run this before executing methods 4 and 5 DBCC UPDATEUSAGE ('database_name','table_name')...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/t-sql/archive/2007/11/02/2666.aspx