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 |
|
marginerazvan
Starting Member
14 Posts |
 Posted - 2008-10-08 : 05:10:12
|
| What query will be quicker?SELECT TOP 1 FROM ...SELECT COUNT(*) FROM ...SELECT COUNT(ID) FROM ...Thank you in advance |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-08 : 05:39:05
|
| No one will give you an answer to that.Run your queries in Management studio and check the time responses. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-08 : 05:41:24
|
| did you test all of them? what did you notice? |
|
|
|
marginerazvan
Starting Member
14 Posts |
Posted - 2008-10-08 : 05:50:08
|
| I tested them and I saw something very interesting:On SQL2005 the quicker it would be SELECT TOP 1SELECT COUNT is much slower (booth of them)On SQL2000 the quicker it would be SELECT COUNT (booth of them)SELECT TOP 1 is much slowerNow, questions:1. did they (Microsoft) change the way COUNT is implementing in SQL2005 than in SQL2000?2. is it true that SELECT COUNT(ID) is quicker than SELECT COUNT(*)Thank you |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-10-08 : 06:07:29
|
| count(*) select all your columns and thereby utilizes more resources, count(ID) selects only a specific column |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-08 : 06:09:13
|
| that all depends on factors like what column you order by in top 1,presence of clustered index,... |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-08 : 10:49:42
|
| What the hell has top 1 got to do with count(*)? I'm completely lost here.Anyway, in answer to your question, count(*) counts the rows. Count(ID) counts the non null IDS. They may or may not execute the same depending on what indexes and nullability you have. Potentially they are totally different queries.Afrika - you are dead wrong. The database engine can implement this however it wants and will in all probability not access all columns. Using the PK or other index would get you the same result for count(*). If there was no index then it would have to table scan of course but that is true of all queries. Same goes for count(column). No need to scan the table if there is an appropriate index, but remember they are not always interchangable. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-08 : 14:11:41
|
| someone needs to chill |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-09 : 09:42:37
|
| You think? I thought I just answered the question. Do you have a contribution? |
|
|
|
|
|
|
speed improvement