Good question, but I think it is being confused, including by me, with an UPSERT (as the previous posts' referral to MERGE indicates too).
This question needs more information to answer correctly: How is the action dealt with if the INSERT is not performed? An UPDATE instead? Maybe we actually want a unique constraint on the Name column to report an error if a name is attempted to be inserted twice? If we do need to update instead if the record already exists, how do we control concurrency in that case?
In high concurrency designs these and many more questions must be answered first and I completely agree a more deterministic approach would be required.
A big misconception I keep coming across is that there must be a perfect singleton solution for all situations.... but why not perfect the solution for each singleton situation? A welcome side-effect of this is that it keeps us all in jobs!
I agree. I think that the question should have stated "what is the most efficient
Depending upon what is to happen if the value already exists, would also possibly influence the choice of method to be used. In this simple
example with only one column, I note the following (performance and locking issues aside):
1. The first option (IF statement) has the capability of returning information back to the UI to inform the user that the value already exists.
2. Option 2 would have to check the ROWCOUNT from the insert to see if the row was inserted and then pass the appropriate information back. Otherwise, the application would [wrongly] assume that the row was added. So we're still coding an IF statement.
And I wouldn't, in this simple case, use the new SQL Server 2008 MERGE statement to update a value to itself.
(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.