Recently I’ve been reading some various other blogs about this issue.
Is SELECT 1 faster (better) then SELECT * when used in an EXISTS (or similar) clause?
These are two blogs I often read which discuss the issue as well:
http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/
and
http://www.sqlskills.com/blogs/conor/2008/02/07/EXISTSSubqueriesSELECT1VsSELECT.aspx

My own personal testing have not shown any difference between the two and my “guesstimate” is that the query optimizer in the database is clever enough to judge from the syntax that it doesn’t actually need to return anything when the SELECT * is in an EXISTS, and thus it doesn’t do much else then evaluate whether the statement is true, just as if it was SELECT 1.

I’ve looked at the query plans, just as P. Dave did (the first link), and my findings were exactly the same. No difference in execution at all.

Then I tried to use the SET STATISTICS TIME ON and SET STATISTICS IO ON combined with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear the cache. This way I could see the time it took to handle the query and no result was conclusive.
The result varied from one case to the other case, so my own testing seems to conclude that IF there is a difference, it looks to be minor.
Granted, I used only a small number of columns to test with, ranging from 2 up to 20, with between 10.000 and 100.000 rows, so perhaps for very large sets, it would be different.

If anybody have any code which consistently shows that SELECT 1 is faster, it’d very much like to see it, because it is one of those SQL myths out there which are hard to prove/disprove.