SQL Server Performance

Difference between count(*) and count(1)

Discussion in 'General Developer Questions' started by muraliprofession, Aug 1, 2007.

  1. muraliprofession New Member

    Dear all,
    Please guide me what is the impact on writing the query as count(*) or count(1) in the select query,as both of them will show the number of records in the table.
    Thanks and Regards,
  2. Adriaan New Member

    The 'normal' parameter for COUNT is * - in most cases there is no reason to use anything else.
    You are free to use any constant expression instead of * - you can use COUNT(0), COUNT('a') and even COUNT(NULL) - each with the same results.
    You can also use a column name as the parameter, but this means that rows with a NULL on that column will not be counted.
    If the column name is from a table on the 'outside' of an outer join (like Table1 LEFT JOIN Table2) then the same rule applies as for a COUNT on any other column name - so if there is no match on Table2, then COUNT(Table2.col) returns 0.
  3. satya Moderator

    Both will be same in terms of performance, as the execution is to retrieve information from the table.
    Article in this regard.
    BTW n the real terms how big is the table and are you getting any performance issues?
  4. Madhivanan Moderator

    Set the execution plan and see. You wont find much difference.

Share This Page