title: Select / Insert version of an Upsert: is there a design pattern for high concurrency?
body: <p>I want to do the SELECT / INSERT version of an UPSERT. Below is a template of the existing code:</p>
  
  <pre><code>// CREATE TABLE Table (RowID INT NOT NULL IDENTITY(1,1), RowValue VARCHAR(50))
  
  IF NOT EXISTS (SELECT * FROM Table WHERE RowValue = @VALUE)
  BEGIN
     INSERT Table VALUES (@Value)
     SELECT @id = SCOPEIDENTITY()
  END
  ELSE
     SELECT @id = RowID FROM Table WHERE RowValue = @VALUE)
  </code></pre>
  
  <p>The query will be called from many concurrent sessions. My performance tests show that it will consistently throw primary key violations under a specific load.</p>
  
  <p>Is there a high-concurrency method for this query that will allow it to maintain performance while still avoiding the insertion of data that already exists? </p>