title: Insert unique sequence number based on another column value
body: <p>I have this SQL Server 2008 R2 table (t1):</p>
  
  <pre><code>+----+------+----------+
  | id | type | sequence |
  +----+------+----------+
  | 1  | 'A'  | 1        |
  | 2  | 'A'  | 2        |
  | 3  | 'A'  | 3        |
  | 4  | 'B'  | 1        |
  | 5  | 'B'  | 2        |
  | 6  | 'C'  | 1        |
  +----+------+----------+
  </code></pre>
  
  <p>For example to insert the next sequence for type 'B' which must be 3, I use:</p>
  
  <pre><code>void Add(string type)
  {
       int i = db.ExecuteScalar("select coalesce(max(sequence) + 1, 1) from t1 where type='" + type + "'"); 
       db.ExecuteNonQuery("insert into t1 (type, sequence) values ('" + type + "', " + i + ")");
  }
  
  Add("B");
  </code></pre>
  
  <p>This works fine, but if two users execute Add("B") exactly at the same time <strong>they will get and insert the same sequence</strong>!</p>
  
  <p>How should I prevent this from occuring?</p>