Fast Row Counting in SQL Server

I love it when I discover something like this…

I’m querying a table in SQL Server that has over 200 million rows. I’m just wanting the row count. So, naturally, I tried the following:

select count(*) from TABLE_NAME

The query took one minute when it was run initially. And select (1) performed no better. (See: SQL count(1) is not better than count(*))

To be fair, I was quite shocked that it took so long to get this value. Surely there was some value stored internally that I could just pluck out in the blink of an eye?

I searched around for this information, and stumbled across this: How can I get sysindexes information in Sql 2005?

So here it is. Takes no time at all to run:

select object_name(i.object_id) as objectName, sum(p.rows) as rowCnt, i.name as indexName
from sys.indexes i
join sys.partitions p on i.object_id = p.object_id
and i.index_id = p.index_id
where i.index_id <= 1
and i.object_id = object_id('TABLE_NAME')
group by i.object_id, i.index_id, i.name

And, if I need, I can strip out the second-last line to get it for all tables.

As it turns out, you can do the equivalent in SQL Server 2000:

select object_name(id), rowcnt
from sysindexes
where indid < 2
and id = object_id('TABLE_NAME')
order by 2

Which will run in 2005 as well, although its legacy, so we must discourage that! You can tell this, because SQL Management Studio doesn’t colour in sysindexs green, but SQL 2000 Query Analyzer does.

Leave a Reply