Ever wondered why it takes so much time to get the count of rows in an SQL table with millions of records? In case when the table is large and fragmented the ‘select count(*) from large_table_name’ may take seconds to minutes to return an output. Re-indexing can also take up considerable amount of time.
System tables can come to the rescue in this case. There is a system table that stores the live meta information of all the tables, it is sysindexes. To retrieve the count of rows from any table, please use the following query:
select rows from sysindexes where id = object_id('large_table_name') and indid<1Also for small sized tables using ‘Select count(1) from small_table_name‘ is more effective and less resource intensive than using ‘select count(*) from small_table_name‘
Post comments if this helps
