IF EXISTS before INSERT, UPDATE, DELETE for optimization

I'm not completely sure, but I get the impression that this question is really about upsert, which is the following atomic operation:

• If the row exists in both the source and target, UPDATE the target;
• If the row only exists in the source, INSERT the row into the target;
• (Optionally) If the row exists in the target but not the source, DELETE the row from the target.

Developers-turned-DBAs often naïvely write it row-by-row, like this:

-- For each row in sourceIF EXISTS(<target_expression>)    IF @delete_flag = 1        DELETE <target_expression>    ELSE        UPDATE target        SET <target_columns> = <source_values>        WHERE <target_expression>ELSE    INSERT target (<target_columns>)    VALUES (<source_values>)

This is just about the worst thing you can do, for several reasons:

• It has a race condition. The row can disappear between IF EXISTS and the subsequent DELETE or UPDATE.

• It's wasteful. For every transaction you have an extra operation being performed; maybe it's trivial, but that depends entirely on how well you've indexed.

• Worst of all - it's following an iterative model, thinking about these problems at the level of a single row. This will have the largest (worst) impact of all on overall performance.

One very minor (and I emphasize minor) optimization is to just attempt the UPDATE anyway; if the row doesn't exist, @@ROWCOUNT will be 0 and you can then "safely" insert:

-- For each row in sourceBEGIN TRANUPDATE targetSET <target_columns> = <source_values>WHERE <target_expression>IF (@@ROWCOUNT = 0)    INSERT target (<target_columns>)    VALUES (<source_values>)COMMIT

Worst-case, this will still perform two operations for every transaction, but at least there's a chance of only performing one, and it also eliminates the race condition (kind of).

But the real issue is that this is still being done for each row in the source.

Before SQL Server 2008, you had to use an awkward 3-stage model to deal with this at the set level (still better than row-by-row):

BEGIN TRANINSERT target (<target_columns>)SELECT <source_columns> FROM source sWHERE s.id NOT IN (SELECT id FROM target)UPDATE t SET <target_columns> = <source_columns>FROM target tINNER JOIN source s ON t.d = s.idDELETE tFROM target tWHERE t.id NOT IN (SELECT id FROM source)COMMIT

As I said, performance was pretty lousy on this, but still a lot better than the one-row-at-a-time approach. SQL Server 2008, however, finally introduced MERGE syntax, so now all you have to do is this:

MERGE targetUSING source ON target.id = source.idWHEN MATCHED THEN UPDATE <target_columns> = <source_columns>WHEN NOT MATCHED THEN INSERT (<target_columns>) VALUES (<source_columns>)WHEN NOT MATCHED BY SOURCE THEN DELETE;

That's it. One statement. If you're using SQL Server 2008 and need to perform any sequence of INSERT, UPDATE and DELETE depending on whether or not the row already exists - even if it's just one row - there is no excuse not to be using MERGE.

You can even OUTPUT the rows affected by a MERGE into a table variable if you need to find out afterward what was done. Simple, fast, and risk-free. Do it.

That is not useful for just one update/delete/insert.
Possibly adds performance if several operators after if condition.
In last case better write

update a set .. where ..if @@rowcount > 0 begin    ..end

You should not do it for UPDATE and DELETE, as if there is impact on performance, it is not a positive one.

For INSERT there might be situations where your INSERT will raise an exception (UNIQUE CONSTRAINT violation etc), in which case you might want to prevent it with the IF EXISTS and handle it more gracefully.