Wednesday, December 3, 2008

Faster way to get rowcount on sql table


If you want to get the number of rows of a sql table, you would probably think of using count keyword of sql server. If "retailer" is a sql table, then it would look like this.


select            count(*)

from     retailer


It gives the result, but it would be costlier in case of CPU usage and time as it uses an aggregate function count.

The query can be optimized as follows.


select rowcnt

from sysindexes

where id = object_id('retailer')

and indid = 1


sysindexes is a system table that contains details of indexes and tabular objects (table, views). This query will have lesser cost compared to previous one so, results faster. It may be beneficial when there are millions of data in the table or when row count is to be obtained repeatedly.