Pat

• Aug 14th, 2006

 

I've always believed count(1) was faster as it doesn't have to return all columns, just the row identity. But logically speaking the optimiser should take care of that so they should translate into the same underlying code

Rashmi121

 Profile Answers by Rashmi121 

• May 9th, 2008

 

count (1) is faster than count(*) because count(*) returns all the rows as given in the select stmt including duplicate and null values while count(1)  or count(expr) will return all non-null values in column 1.

tumeshk

 Profile Answers by tumeshk 

• Jun 14th, 2008

 

count(1) will be faster than count(*) because it counts the first column values with not null

gcvpgeek

 Profile Answers by gcvpgeek 

• Jun 23rd, 2009

 

count(1) is faster than count(*)

chrisjej

 Profile Answers by chrisjej 

• Jun 23rd, 2010

 

select count(*) does not need to fetch all column data - which you can see whenever the showplan chooses to perform the query using an index.

I doubt there is a difference - and when I just tried to benchmark this, the CPU time and logical reads were identical.

The following will usually (but not always) return the correct count of a table:
    select sum(ROWCNT(i.doampg))
    from sysindexes i,
     sysobjects u
    where i.id =  u.id
    and u.type = "U"
    and u.name = "$table_name"

and is extremely fast.