Recently I came across a fix that needed me to optimize performance as Count(*) used a lot of memory. My fix was Count(Col_name) which yielded wrong results. So I decided to pen down this based on some google search:
Doing
It counts the results of the query placed inside of it. SQL Server does a trick with the * in COUNT(*). It does count the rows. But COUNT([Name]) counts only the results that are not NULL. This can throw your counts off if it is not what you are expecting. So use COUNT(1) to be sure of what you are getting in spite of NULL.
See the below script and result:
Doing
COUNT(*) on a table with a lot of columns and a lot of
rows can take a lot of time and memory. There is a temptation to do COUNT([Name])
where [Name] is the name of a column in the table. This can produce some surprising
results. This is because of the way that COUNT() works.It counts the results of the query placed inside of it. SQL Server does a trick with the * in COUNT(*). It does count the rows. But COUNT([Name]) counts only the results that are not NULL. This can throw your counts off if it is not what you are expecting. So use COUNT(1) to be sure of what you are getting in spite of NULL.
See the below script and result:
