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.
The first way to check will come in head is with
count(*) like below snippet, right?
select count(*) from [tableName];
This is costly and unnecessarily selects all the columns. Bit faster than earlier one is below:
select count(1) from [tableName];
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