Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ya3mro
Starting Member
37 Posts |
 Posted - 2006-05-18 : 08:09:21
|
| i have a small Question in my mind ::which is best for us when we want to get the total count of rows ?1- Select count(*) from Table_Name and2- Select count(ID) from Table_Nameif i put ColumnName in () .. Is it better than Count(*)plz explain it to methanxhttp://english.islamway.com |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-18 : 08:25:32
|
| COUNT(*) is the best - the idea is that the "*" allows SQL Server to choose which column it will use.Select count(ID) from Table_Namewill count the number of rows where ID is NOT NULL. For a column that has no NULL values that's the same as the total number of rows, but for other columns it won't be!Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-18 : 08:57:49
|
| >>COUNT(*) is the best - the idea is that the "*" allows SQL Server to choose which column it will use.Did you mean Index will be used if any column has it?MadhivananFailing to plan is Planning to fail |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-05-18 : 09:01:20
|
| To clarify the first Kristen´s comment: When you use count(*) sql server will use the narrower index to count number of rows. I am not sure when the column is not nullable if query optimize wouldn't do the same anyway. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-05-18 : 09:10:25
|
| Yes, if col is a not nullable column, COUNT(col) and COUNT(*) will result in the same execution plan, even to the extent of using an index that doesn't contain col.Even so, I use COUNT(*) because it's clearer to the reader that it's intended to be counting rows, not non-null values of a particular column. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-18 : 09:39:40
|
| "Did you mean Index will be used if any column has it?"Not particularly - I meant its just up to SQL Server which one it uses - in effect that will be the PK I suppose, but I like Arnolds reasoning better:"it's clearer to the reader that it's intended to be counting rows, not non-null values of a particular column"Kristen |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-05-18 : 20:22:41
|
Another classic. If you don't know which to use then you don't know what question you are asking. Neither is "better" - they do different things. If they were the same there wouldn't be two different functions would there?
Anyway - my rant aside - everyone else has probably given you the answer you were looking for. count(*) counts rows, count(xxx) counts the non null values for xxx. Count(1) is for people who listen to urban ledgends! |
|
|
|
|
|
|
Which is Best Select Count(*) , Count(id)