Alternative way of finding the rowcount of a table

To get the total row count in a table, we usually use the following select statement:

SELECT count(*) FROM table_name

But the problem with this query is

It performs full table scan to get the row count.

If you enable your execution plan you can know this (Go to Query-> Choose Show execution Plan).

So if you have a table with say lakhs of rows, it gives a performance hit.

There is another way to determine the total row count in a table. You can use the sysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

Please remember that this query works irrespective of whether the table has any indexes or not.

But for the second query if you see the execution plan you would find a index scan or an index seek based on the column you have used in your search criteria