title: How to generate an integer unique id in a multi-user application without duplication in C#/SqlServer?
body: <p><strong>Scenario</strong></p>
  
  <p>I want to generate a unique integer key (not a database primary key) by extracting max() and incrementing it by one. Then I want to use that integer key for insert/update operations in one or more tables. I prefer to do this in C# code but if there's no other option then I could go for SQL batch statements or inside a stored procedure etc as well.</p>
  
  <p><strong>Question</strong></p>
  
  <p>Since multiple users can be doing this at the same time, how do I ensure that no two users get the same max()? </p>
  
  <p><strong>Sample pseudocode</strong>
  Let's say there are two tables - Employee (Columns: EmpId, BatchId, Name) and MiscData (Columns: EmpId, BatchId). </p>
  
  <p>Below is the C# inspired pseudocode that shows one implementation of this scenario -</p>
  
  <pre><code>void DoOperation(int empId, string[] names)
  {
      int maxBatchId = repository.GetMaxBatchId(); //GetMaxBatchId() basically executes select max(BatchId) from Employee
      maxBatchId++;
      foreach(string name in names)
          command.ExecuteNonQuery("insert into Employee values (" + maxBatchId + ", '" + name + "')");
      command.ExecuteNonQuery("insert into MiscData select EmpId, " + maxBatchId + " where EmpId = " + empId);
  }
  </code></pre>
  
  <p>The method DoOperation above can be doing whatever database operations based on the value of maxBatchId + 1.</p>
  
  <p>If more than one users run DoOperation(...) at the same time, they're likely to get exactly same maxBatchId. How do I ensure that only a single instance of this method can run at one time?</p>