Often we may need to check the number of rows in a `table`. If the table size is not big enough, then need not to worry much about that. But what if your table has more than `10M rows`? You will find the first two approach described in below is very slow and may lead to lock the table operation.

Approach 1:

The first way to check will come in head is with count(*) like below snippet, right? 

select count(*) from [tableName];

Approach 2:

This is costly and unnecessarily selects all the columns. Bit faster than earlier one is below:

select count(1) from [tableName];

Approach 3:

But the fastest one is the below one, as it takes the advantage from the direct index heap. 

select sum([rows])
from sys.partitions
where object_id=object_id('tablename')
and index_id in (0,1);
Note: please keep in mind that, the fastest way to get the number of rows in a table will give you an approximate row count.

Tagged in:

SQL