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:
>

identity fields - losing values

Hi,
I've an application that consists of a of main table. Each record requires a
numeric reference and these references must be sequential with no gaps.
At the moment, this reference field is of an identity type. This works fine
most of the time, however every now and again the identity field 'loses' a
number (for example goes from 58 to 60) which is a right pain for me.
From my limited understanding of TSQL I see two alternatives
1. Have a manual identity field which I increment manually and use locking
and error trapping to ensure that no values are 'lost'. However, I'm not
really sure how to achieve this...
2. Even if when an identity value is 'lost' it is marked as such is fine, I
simply cannot have any gaps. Can I keep the identity value in place but use
error trapping to ensure that when an insert fails the identity is marked
appropriately rather than simply 'lost'.
Which of the above methods is the recommended one (or are there any other
alternatives) and can any kind soul point me in the right direction as to
how to achieve this (for example, links, etc).
Any and all advice is gratefully received.
Kind regards
Chris.You can't really avoid gaps with an IDENTITY column. If you care about
the value inserted then IDENTITY is the wrong solution.
You haven't explained how this value is to be used. If it is to be
purely based on insertion order then maybe you don't even need it in
the table. Just add a Creation Date column, order by that and display
the row number client-side or in a query.
You can increment a value yourself on the INSERT:
INSERT INTO YourTable (x, ...)
SELECT COALESCE(MAX(x),0)+1, ...
FROM YourTable
but this effectively serialises every INSERT, which may not be
acceptable in a multi-user environment. Logically there isn't a way out
of this: You can't allow concurrent updates if you need to maintain a
serial key in real-time because a rolled-back transaction will always
leave a gap.
David Portas
SQL Server MVP
--|||> 2. Even if when an identity value is 'lost' it is marked as such is
fine, I
> simply cannot have any gaps. Can I keep the identity value in place
but use
> error trapping to ensure that when an insert fails the identity is
marked
> appropriately rather than simply 'lost'.
If you mean that you just want to show the missing values in the data,
you can do so like this:
SELECT
N.num AS id, T.*
FROM Numbers AS N
LEFT JOIN YourTable AS T
ON N.num = T.id
WHERE N.num BETWEEN 1 AND 999999999
Where Numbers is a table containing the total range of IDs.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112956106.268466.113170@.f14g2000cwb.googlegroups.com...
> You can't really avoid gaps with an IDENTITY column. If you care about
> the value inserted then IDENTITY is the wrong solution.
> You haven't explained how this value is to be used. If it is to be
> purely based on insertion order then maybe you don't even need it in
> the table. Just add a Creation Date column, order by that and display
> the row number client-side or in a query.
> You can increment a value yourself on the INSERT:
> INSERT INTO YourTable (x, ...)
> SELECT COALESCE(MAX(x),0)+1, ...
> FROM YourTable
> but this effectively serialises every INSERT, which may not be
> acceptable in a multi-user environment. Logically there isn't a way out
> of this: You can't allow concurrent updates if you need to maintain a
> serial key in real-time because a rolled-back transaction will always
> leave a gap.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Many thanks for your reply. For some background, the reference is simply a
way of identifying each record which as you suggested is based on insertion
order (although to be honest - this isn't terribly important - it wouldn't
be a problem for record X to have a lower ID than Y even if X was inserted
after Y).
I realise that ideally this wouldn't be a requirement as long as the ID is
unique but there is some inertia from another department who see gaps as
indicating missing records (and despite my best efforts can't be convinced
otherwise).
Based on what you've said, how does this sound:
Have a IDStore table. Two fields, "ID" and "committed". When an insert is
attempted, the first ID is found which is uncommitted and this is used. If
no uncommitted ID's are found then a row is inserted into the IDStore table,
with the next free ID and an uncommitted value. When the insert is
successful, the committed field is marked as committed. If it is
unsuccessful it is marked as uncommitted. And so on.
Assuming that I've managed to explain it clearly, does this sound like a
reasonable approach? I realise that I'll have to investigate locking and so
on but at least its something I can work towards..
Once again, your advice is gratefully received.
Chris.|||This is a risk whenever you expose IDENTITY to users. The IDENTITY
value acquires business meaning for the users and then you are lost
because there are too many scenarios in which you can't control the
IDENTITY value. The best policy is not to expose IDENTITY to users at
all.
Assuming the users don't have direct access to the tables (not usually
a good idea to allow this anyway), just don't display the IDENTITY,
which after all should only be used as an artificial key. I assume your
table has an alternative, business key as well. If not then you have a
more serious design problem. IDENTITY should never be the only key of a
table.
If the users need the "comfort factor" of seeing the numbers then see
my other reply on how to fill in the gaps. As you suggest, there are
alternative strategies but they all involve serializing the inserts, or
accepting that there will be gaps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> This is a risk whenever you expose IDENTITY to users. The IDENTITY
> value acquires business meaning for the users and then you are lost
> because there are too many scenarios in which you can't control the
> IDENTITY value. The best policy is not to expose IDENTITY to users at
> all.
> Assuming the users don't have direct access to the tables (not usually
> a good idea to allow this anyway), just don't display the IDENTITY,
> which after all should only be used as an artificial key. I assume your
> table has an alternative, business key as well. If not then you have a
> more serious design problem. IDENTITY should never be the only key of a
> table.
> If the users need the "comfort factor" of seeing the numbers then see
> my other reply on how to fill in the gaps. As you suggest, there are
> alternative strategies but they all involve serializing the inserts, or
> accepting that there will be gaps.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Thanks again for your reply. Based on your advice, I have redesigned the
table in question so that the IDENTITY field is internal to the system and
not visible by the user.
I have created an additional, INT field in this table which will be used for
the reference and will be gapless and sequential - although I've yet to
figure out exactly how to achieve this (but I see triggers in use)...
Thanks once again,
Chris.|||CHris,
Does this new field (we say "column" in SQL btw) have to be immutable?
Will they throw a fit if the record 227 becomes record 226 after someone
deletes record 200?
Will it be impossible to delete records from the table?
If any of these answers are NO, And if the performance hit is acceptable,
then I might suggest that you not persist a value in this new column at all,
Just calculate it on display, as the Count of all other records in the table
with Identity Value <= IDentity value of the record you're displaying
Select <Other COls>,
(Select Count(*) From Table
Where ID <= T.ID) As RowID
From Table T
"Chris Strug" wrote:

> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> David,
> Thanks again for your reply. Based on your advice, I have redesigned the
> table in question so that the IDENTITY field is internal to the system and
> not visible by the user.
> I have created an additional, INT field in this table which will be used f
or
> the reference and will be gapless and sequential - although I've yet to
> figure out exactly how to achieve this (but I see triggers in use)...
> Thanks once again,
> Chris.
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:992173F7-F444-4C7E-821A-A18B4F385944@.microsoft.com...
> CHris,
> Does this new field (we say "column" in SQL btw) have to be immutable?
> Will they throw a fit if the record 227 becomes record 226 after someone
> deletes record 200?
> Will it be impossible to delete records from the table?
> If any of these answers are NO, And if the performance hit is acceptable,
> then I might suggest that you not persist a value in this new column at
all,
> Just calculate it on display, as the Count of all other records in the
table
> with Identity Value <= IDentity value of the record you're displaying
> Select <Other COls>,
> (Select Count(*) From Table
> Where ID <= T.ID) As RowID
> From Table T
Hi!
Unfortunately the answer to all your questions is a resounding "yes!".
However, the example you mention is something that I've crossed before and I
feel will be very useful sooner or later.
Thanks for your advice though!
Chris.