Generally we use
Select count(*) from emp
to count the no of rows but actually it slow down the speed
statement might consume significant resources if your tables are very
big because scanning a large table or index can consume a lot of I/O.
Avoid these type of queries to the maximum
is one other way you can find the total number of rows in a table.
SQL Server Books Online (BOL) documents the structure of sysindexes;
the value of sysindexes.indid will always be 0 for a table and 1 for
a clustered index. If a table doesn't have a clustered index, its
entry in sysindexes will always have an indid value of 0. If a table
does have a clustered index, its entry in sysindexes will always have
an indid value of 1.
Here is the query used to count the rows of the table using sysIndexes:
rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2