SQL Server Performance Forum – Threads Archive

COUNT(*) efficient?

Is the query optimizer intelligent enough to know that COUNT(*) doesn’t need to retrieve all columns, and should just get a count?

---

I think sql server will only count the rows, using * means it won’t check any column, judging by the speed of it I very much doubt it returns any row information, just a numeric count of the rows in the table. Cheers
Shaun World Domination Through Superior Software

---

That is correct. The idea of a table is that they are collections of rows, and those rows are made up of columns. Because of this, when you do a COUNT(*), the query engine will simply count how many rows are in the table. However, if you put a column name in the count, such as COUNT(myField), it will do a count of the particular column you specified in each row. On top of that, it will only count rows in which the field you specified is not null.

---

even though count(*) does not need to retrieve column information,
it still must get an accurate count of the rows in a table,
which means examining each page of either the table or or one of the indexes count(*) usually involves a table or index scan unless you specified a SARG
the only reason it may be cheaper than a full table scan is that it is possible to get a count by scanning an index, which is more compact than the full table so i would avoid excessive use of count(*) on large tables without row limiting SARGs

---

Here is an article on the subject:
http://sql.nu/articles/count.shtml /Argyle

---

U can find the clear notes on thin in Sql server Books on line(Topic is Count Aggregate function). Rushendra

---

]]>