Naomi,

There are a lot of variables that drive query plans, what works well for a smallish lookup table isn't the same for a large transaction table. And query plans only show one very narrow view of what's going on inside the engine. You really need to look at the I/Os that happen to verify that excessive page reads are not happening.

For me now though the rule is to use this form of EXISTS check

where exists ( select 'x' from ... )

Also FWIW I've found that SQL2005 doesn't do near as good a job as SQL2000 on some query plans. I've found that hints on some joins and where clauses have radically improved performance in 2005 where they were not needed in 2000.

>See also http://www.zimbio.com/SQL/articles/589/SQL+SERVER+SELECT+1+vs+SELECT+Interesting
>
>Not every one agrees.
>
>http://forums.asp.net/t/1374663.aspx