Is there any difference...

...in speed between
if exists(select top 1 id from table)
or
if exists(select id from table)
Thanks.No.
But just in case you want proof, then check the query plan. If they are
the same, then you are sure their performance is the same.
Gert-Jan
Frank Rizzo wrote:
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
> Thanks.|||Shouldn't there be a WHERE clause on that SELECT?
Since the EXISTS stops processing once it finds a single record that
matches, I would think adding TOP would just make it do more work.
I've read that you should always use *, as in IF EXISTS (SELECT * FROM TABLE
WHERE ...) because that allows the query optimizer to choose whichever field
it thinks is best.
Greg
"Frank Rizzo" <none@.none.com> wrote in message
news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
> ...in speed between
> if exists(select top 1 id from table)
> or
> if exists(select id from table)
>
> Thanks.|||Gert-Jan Strik wrote:
> No.
> But just in case you want proof, then check the query plan. If they are
> the same, then you are sure their performance is the same.
Thanks for the tip. The query plan is absolutely identical.
[vbcol=seagreen]
> Gert-Jan
>
> Frank Rizzo wrote:
>|||These are all equivalent.
if exists (select * from pubs..authors) print 1
if exists (select top 1 * from pubs..authors) print 1
if exists (select au_id from pubs..authors) print 1
if exists (select 1 from pubs..authors) print 1
The presence of a TOP 1 does not affect the result, because what matters is
whether a row is returned or not by the subquery. For the same reason, the
content of the select list (* vs. 1 vs. one or more columns) does not matter
either. This is still the case when the subquery contains a WHERE clause. I
second Gert-Jan's suggestion that this is easily provable by comparing the
query plans.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Burns" <bluebunny@.newsgroups.nospam> wrote in message
news:%23jKl2Am4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Shouldn't there be a WHERE clause on that SELECT?
> Since the EXISTS stops processing once it finds a single record that
> matches, I would think adding TOP would just make it do more work.
> I've read that you should always use *, as in IF EXISTS (SELECT * FROM
> TABLE WHERE ...) because that allows the query optimizer to choose
> whichever field it thinks is best.
> Greg
> "Frank Rizzo" <none@.none.com> wrote in message
> news:%23sbGSHl4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>