What is the preferred merge method for SQL Server 2005?

Question

I have mainly been using the Exists Method for merging a row into a table but I am considering switching to the Row Count Method. Is there any reason not to?

Exists Method

If Exists(Select * From Table Where ID = @ID) Begin

    Update Table Set Value = @Value Where ID = @ID  

End Else Begin

    Insert Into Table (Value) Values (@Value);   

End

Row Count Method

Update Table Set Value = @Value Where ID = @ID 

If (@@RowCount = 0) Begin

    Insert Into Table (Value) Values (@Value);   

End

Performance

The Row Count Method seems to be dramatically faster. On a table with about 50k rows it clocks in at 1/5 the time of the Exists Method. The tests were not too scientific but even with a conservative +/- 15% that is considerable. This is the main reason I want to switch.

NOTE

The examples were purposely made simple for readability. They in no way reflect my actual situation.

Answer 1

Either way, you might need to wrap that in a transaction.

Answer 2

I don't see any particular problem. You would have to try which one is more performant, though (although I think that's insignificant in this example). But as Cade pointed out, use a transaction.

Also, note that for SQL Server 2008 you can use the MERGE statement (just in case you are going to upgrade).

Answer 3

The biggest reason not to switch is that what you have now is working and making a change introduces the possibilty of new bugs creeping in. If you want to change as you update other things, that's OK, but what are you really going to gain from the change? I suspect the performance gain if any would likely be very small as the examples you are giving appear to use single records.