T-SQL Insert or update

Question

I have a question regarding performance of SQL Server.

Suppose I have a table persons with the following columns: id, name, surname.

Now, I want to insert a new row in this table. The rule is the following:

1. If id is not present in the table, then insert the row.

2. If id is present, then update.

I have two solutions here:

First:

update persons
  set id=@p_id, name=@p_name, surname=@p_surname
where id=@p_id
if @@ROWCOUNT = 0 
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

Second:

if exists (select id from persons where id = @p_id)
  update persons
    set id=@p_id, name=@p_name, surname=@p_surname
  where id=@p_id
else
  insert into persons(id, name, surname)
  values (@p_id, @p_name, @p_surname)

What is a better approach? It seems like in the second choice, to update a row, it has to be searched two times, whereas in the first option - just once. Are there any other solutions to the problem? I am using MS SQL 2000.

Answer 1

Option 1 seems good. However, if you're on SQL Server 2008, you could also use MERGE, which may perform good for such UPSERT tasks.

Note that you may want to use an explicit transaction and the XACT_ABORT option for such tasks, so that the transaction consistency remains in the case of a problem or concurrent change.