IF NOT EXISTS vs @@ROWCOUNT - Performance

Question

Which one should I use for the best performance?

This one:

IF NOT EXISTS (...where Id = 'x')
    INSERT...'x'
ELSE 
    UPDATE...WHERE Id = 'x'

Or this one:

UPDATE...WHERE Id = 'x'
if @@ROWCOUNT = 0
    INSERT...'x'

Answer 1

I don't think performance is going to matter much between the two versions you proposed.

To me, the real question is how to deal with database activity between your statements. What happens if someone tries to INSERT a row immediately after the NOT EXISTS check in your first example or the UPDATE in your second example? Is this happening inside a transaction? If so, what is your isolation level?

MERGE would be better, but you said that's not an option because you need to support SQL Server 2005.

What we've done for SQL Server 2005 is to perform both the INSERT and the UPDATE. Start with the UPDATE, so you don't UPDATE a row you just created with an INSERT:

UPDATE Persons
SET PersonName = 'Ted'
WHERE PersonID = 6

;WITH Data(Id, Name)
AS (SELECT 6, 'Ted')
INSERT MyTable (PersonId, PersonName)
SELECT Id, Name
FROM Data
    LEFT JOIN Persons ON PersonId = Id
WHERE PersonId IS NULL -- the person does not already exist

This routine makes more sense for larger bulk operations where some of the data might already exist, but it can be used for a single update.

Another consideration: do you need to track if someone else changed the row? If so, I'd recommend adding a timestamp(rowversion in newer version) column and failing the UPDATE if the timestamp is different.

For example, say Alice is editing the person ID 6 named 'Bill'. After she opens Bill's row, Charlie opens Bill's row, enters some information, and saves the changes. When Alice saves her changes, she will overwrite Charlie's changes.