Sergiy (9/7/2008)

---

icocks (9/5/2008)

---

A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.

In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.

My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.

Look on what you are choosing from:

1. A solution which may cause errors in production environment but easy on resources;

2. A solution which is error safe but potentially causes some overhead.

Which one wold you choose for your car?

Are those airbags an overhead?

Have you ever been in a situation when you really needed it?

Have any of you friends?

So when you choose a car - will you prefer the one without airbags?

Why?