2

Hi all i am newbie in SQL? What is the difference between count(*) and count(1) in SQL?

Thank You

bilal
  • 648
  • 8
  • 26

1 Answers1

3

There is no difference.

Select Count(*) from TableName
Select Count(1) from TableName

It is very common perception that the Count(1) perform better compared to Count(), however it is not the case. If you test by looking at the execution plan, you will see same action being performed by both the commands and same number of rows being scanned. The time taken may be slightly different interms of CPU usage for count() , but is almost same as count(1). The confusion is generally because in older version of some RDBMS products like Oracle has difference in performance for select count(*) and count(1), but recent releases does not have any difference.

Refer this : http://www.sqlserverf1.com/difference-between-select-count-and-count1-in-sql-server/

and

Count(*) vs Count(1)

Community
  • 1
  • 1
backtrack
  • 7,996
  • 5
  • 52
  • 99
  • What are the differences ? – bilal Jan 06 '15 at 04:54
  • @bilal There is no difference. .. Check the post : http://stackoverflow.com/questions/1221559/count-vs-count1 – backtrack Jan 06 '15 at 04:55
  • 1
    I think the confusion over the execution cost is also due to `SELECT *` being generally more expensive than `SELECT 1` or `SELECT intColumn`. Of course, `COUNT(*)` would have specific optimizing code in the parser/engine, which would recognize that there is no need to fetch the contents of every column. – Turophile Jan 06 '15 at 06:11