IN a scenario where the user would like to retrieve the Count(*) from a table without any condition, in that case this query will work

select count(*) from

'Suppose product is the table,
if the product table is light then its ok but if it contains a huge data, then this query can be used.

select max(rowcnt) from sysindexes where id in (select id from sysobjects where name='product')

What will happen in this case, it will reduce the no. of rows to fetch from database. What happens is whenever a row is updated in the database, the 'rowcnt' field is also updated in the sysindexes table.

Regards

Sudhir Chawla