Which is better? count(*), count(fieldname) or other

i not 1 asking question. after searching found tons of information, of rather old (2000-2007) , of rather conflicting (not mention not make sense).

this doing:

select count(*) mytable siteid = 233

so want know how many records pertaining site 233 in mytable table.

the requirement solution has compatible sql server 2000 , above.

i researched using both alternatives , results bacically:

• don't use *!!!! instead use other field. using * (really) bad because makes sql server work harder.
• using field nullable return unexpected results if of records have field set null (that invalidates statement above). make sure specify field can't null.
• use name of primary key instead of * or field name.
• using field name (that not nullable) , * same , performance gain not noticeable.

what want determine whether record exists. don't care how many rows in table pertaining site id. may lead alternate method better using count().

perhaps there better (performance wise) way this? time, orientation appreciated. saga

 

you not need count in order prove existence, exists operator enough find out if row exists or not, naomi suggested. now, optimizer can recognize trying prove existence , use same execution plan "if (select count(*) t c1 = 5) > 0 ...".

example:

use northwind;  go  set showplan_text on;  go  if exists (select * from dbo.orders where customerid = n'alfki')  	print '1';  else  	print '0';    if (select count(*) from dbo.orders where customerid = n'alfki') > 0  	print '1';  else  	print '0';  go  set showplan_text off;  go    /*    query 1       |--compute scalar(define:([expr1004]=case when [expr1005] (1) else (0) end))    |--nested loops(left semi join, define:([expr1005] = [probe value]))     |--constant scan     |--index seek(object:([northwind].[dbo].[orders].[customersorders]), seek:([northwind].[dbo].[orders].[customerid]=n'alfki') ordered forward)      query 2     |--compute scalar(define:([expr1005]=case when [expr1006] (1) else (0) end))    |--nested loops(left semi join, define:([expr1006] = [probe value]))     |--constant scan     |--index seek(object:([northwind].[dbo].[orders].[customersorders]), seek:([northwind].[dbo].[orders].[customerid]=n'alfki') ordered forward)    */    

regards how time / effort save using constant in select clause or star (*), prove existence, the answer should pay attention more important things, normalization, indexes, sargability, etc, because the time small , may be not measurable.

 

---

amb

some guidelines posting questions...

SQL Server  >  Transact-SQL