SQL count(*) performance

Mikael Eriksson has a good explanation bellow why the first query is fast:

SQL server optimize it into:if exists(select * from BookChapters). So it goes looking for the presence of one row instead of counting all the rows in the table.

For the other two queries, SQL Server would use the following rule. To perform a query like SELECT COUNT(*), SQL Server will use the narrowestnon-clustered index to count the rows. If the table does not have anynon-clustered index, it will have to scan the table.

Also, if your table has a clustered index you can get your count even faster using the following query (borrowed from this site Get Row Counts Fast!)

--SQL Server 2005/2008SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]FROM sys.sysindexes i WITH (NOLOCK)WHERE i.indid in (0,1)ORDER BY i.rowcnt desc--SQL Server 2000SELECT OBJECT_NAME(i.id) [Table_Name], i.rows [Row_Count]FROM sysindexes i (NOLOCK)WHERE i.indid in (0,1)ORDER BY i.rows desc

It uses sysindexes system table. More info you can find here SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012

Here is another link Why is my SELECT COUNT(*) running so slow? with another solution. It shows technique that Microsoft uses to quickly display the number of rows when you right click on the table and select properties.

select sum (spart.rows)from sys.partitions spartwhere spart.object_id = object_id(’YourTable’)and spart.index_id < 2

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.

select max(ROWS)from sysindexeswhere id = object_id(’YourTable’)

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

try this if you only want to know rows count:

exec sp_spaceused [TABLE_NAME]

If you have a look at the execution plans for your queries you would see what is going on.

Your first query if (select count(*) from BookChapters) = 0 is recognized by the query optimizer as the same as if exists(select * from BookChapters). SQL Server knows that the expression is true if there are at least one row present so it goes looking for the presence of one row instead of counting all the rows in the table.

For your other queries it can't be that smart and have to count the number of rows in the table before it can decide if the expression evaluates to true or false.