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:
U can find the clear notes on thin in Sql server Books on line(Topic is Count Aggregate function). Rushendra