Selecting from sysindexes is technically faster than using count(*), as if a human would ever notice the difference, but the sysindexes value returned may not be accurate if statistics on the table are not up to date. And running UPDATE STATISTICS on all your tables will take a lot of time, so use the count(*) method if you need accuracy.
If it's not practically useful, then it's practically useless.
if you have a large number of rows (say a few million) it's definitely better to go to sysindexes or call sp_spaceused (which hits sysindexes), as long as you don't need need the accuracy of count(*). count(*) is a pretty expensive way to get the count if there are many rows.
I have been reviewing this thread here and have been trying to figure out how to use sysindex
rundra stated that I could use sysindex like this but would I use this command verbatim with exception of the 'table_name'?
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2