IF EXISTS

In Books Online it says "The select list of a subquery introduced by EXISTS
almost always consists of an asterisk (*). There is no reason to list column
names because you are simply testing for the existence of rows that meet the
conditions specified in the subquery."
If I've got two tables in my query should I still do SELECT *?
E.g...
IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
<> deleted.Location)
IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo =
deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
Does it make a difference?
Thanks,
Chris>If I've got two tables in my query should I still do SELECT *?
It doesnt matters.
Personally I prefer SELECT 1
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Chris" <cw@.community.nospam> wrote in message
news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In Books Online it says "The select list of a subquery introduced by
> EXISTS almost always consists of an asterisk (*). There is no reason to
> list column names because you are simply testing for the existence of rows
> that meet the conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE
> inserted.CustomerSerialNo = deleted.CustomerSerialNo AND inserted.Location
> <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
> = deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||No difference. It's the presence or absence of a row that matters, not
what columns there are in that row.
I know some people say that the idea of specifying * is that it lets
the optimizer choose the best plan based on ANY column(s) in the
table/indexes. However, I don't recall ever seeing an example in SQL
Server where the SELECT list in an EXISTS statement makes any
performance difference. AFAIK the optimizer always ignores the columns
you specify. Whatever the case, in logical terms it makes no difference
to the result of the query.
David Portas
SQL Server MVP
--|||Thanks,
Chris
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ONYyWxyRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> It doesnt matters.
> Personally I prefer SELECT 1
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Chris" <cw@.community.nospam> wrote in message
> news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>|||Chris,
I've heard a rumour that select * is a tiny bit faster to execute.
I don't like your old style join syntax though, this may be easier to read:
if exists
( select *
from inserted i
inner join deleted d
on d.CustomerSerialNo = i.CustomerSerialNo
and d.Location <> i.Location
)
Regards
AJ
"Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MSFTNGP15.phx.gbl..
.
> In Books Online it says "The select list of a subquery introduced by EXIST
S almost always consists of an asterisk (*).
> There is no reason to list column names because you are simply testing for
the existence of rows that meet the
> conditions specified in the subquery."
> If I've got two tables in my query should I still do SELECT *?
> E.g...
> IF EXISTS (SELECT inserted.CustomerSerialNo FROM inserted, deleted WHERE i
nserted.CustomerSerialNo =
> deleted.CustomerSerialNo AND inserted.Location <> deleted.Location)
> IF EXISTS (SELECT * FROM inserted, deleted WHERE inserted.CustomerSerialNo
= deleted.CustomerSerialNo AND
> inserted.Location <> deleted.Location)
> Does it make a difference?
> Thanks,
> Chris
>|||Agree. And, I'm pretty certain that we've been told from MS that this is ind
eed the fact (i.e., SQL
Server isn't stupid enough to actually materialize any column data is simila
r - column list in
EXISTS is irrelevant).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
> No difference. It's the presence or absence of a row that matters, not
> what columns there are in that row.
> I know some people say that the idea of specifying * is that it lets
> the optimizer choose the best plan based on ANY column(s) in the
> table/indexes. However, I don't recall ever seeing an example in SQL
> Server where the SELECT list in an EXISTS statement makes any
> performance difference. AFAIK the optimizer always ignores the columns
> you specify. Whatever the case, in logical terms it makes no difference
> to the result of the query.
> --
> David Portas
> SQL Server MVP
> --
>|||Great, thanks for the information.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23frGCO0RFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Agree. And, I'm pretty certain that we've been told from MS that this is
> indeed the fact (i.e., SQL Server isn't stupid enough to actually
> materialize any column data is similar - column list in EXISTS is
> irrelevant).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1114169328.986401.306840@.z14g2000cwz.googlegroups.com...
>|||As David mentioned, we have never seen an example posted that
demonstrated a difference in performance. So I would forget about the
rumour...
Gert-Jan
Andrew John wrote:
> Chris,
> I've heard a rumour that select * is a tiny bit faster to execute.
> I don't like your old style join syntax though, this may be easier to read
:
> if exists
> ( select *
> from inserted i
> inner join deleted d
> on d.CustomerSerialNo = i.CustomerSerialNo
> and d.Location <> i.Location
> )
> Regards
> AJ
> "Chris" <cw@.community.nospam> wrote in message news:OkyubuyRFHA.3156@.TK2MS
FTNGP15.phx.gbl...|||Gert-Jan,
I don't know if I'd call it a performance difference, but if there
are identifiers or literals in the select list of EXISTS, the query
processor must check that they are valid, a step not required
with *. So in at least one sense, SELECT * may be less work
than SELECT 1 or SELECT <column>.
The two queries below produce errors, and I doubt the code that
leads to these errors is executed at all with EXISTS (SELECT * ...).
But I'm sure this code *is* executed with SELECT 1 or SELECT i.
With everything else the query processor has to do, though,
I doubt this difference is measurable, and I believe the
query plan is always the same. Once a plan is cached, also,
there may not be a difference.
create table T (
i int
)
go
select i
from T
where exists (
select X from T
)
go
select i
from T
where exists (
select 9999999999999999999999999999999999999999
from T
)
go
Steve Kass
Drew University
I assume there
Gert-Jan Strik wrote:
>As David mentioned, we have never seen an example posted that
>demonstrated a difference in performance. So I would forget about the
>rumour...
>Gert-Jan
>
>Andrew John wrote:
>|||Right. No example that demonstrates a (measureable) performance
difference!
The code you posted just demonstrates that the query parser does not
allow illegal or inproperly formed queries.
Gert-Jan
Steve Kass wrote:
> Gert-Jan,
> I don't know if I'd call it a performance difference, but if there
> are identifiers or literals in the select list of EXISTS, the query
> processor must check that they are valid, a step not required
> with *. So in at least one sense, SELECT * may be less work
> than SELECT 1 or SELECT <column>.
> The two queries below produce errors, and I doubt the code that
> leads to these errors is executed at all with EXISTS (SELECT * ...).
> But I'm sure this code *is* executed with SELECT 1 or SELECT i.
> With everything else the query processor has to do, though,
> I doubt this difference is measurable, and I believe the
> query plan is always the same. Once a plan is cached, also,
> there may not be a difference.
> create table T (
> i int
> )
> go
> select i
> from T
> where exists (
> select X from T
> )
> go
> select i
> from T
> where exists (
> select 9999999999999999999999999999999999999999
> from T
> )
> go
> Steve Kass
> Drew University
> I assume there
> Gert-Jan Strik wrote:
>