title: MSSQL Can a column auto-increment only under certain circumstances
body: <p>I have a table with 4 columns ID, c1, c2 and LOT. ID is the primary key. For every record when c1 is 5 I want to auto-generate a number for LOT which will be a sequence starting from 1 for each distinct value of c2.</p>
  
  <p>So if c1 is not 5, LOT remains null. But if c1 is 5 then for every record where c2=1 I want to populate LOT with an auto-incrementing sequence starting from 1. 
  Ex:</p>
  
  <pre><code>ID  c1  c2  LOT
  1   3   
  2   5   1   1
  3   5   1   2
  4   5   1   3
  5   4
  </code></pre>
  
  <p>Then do the same for a different value of c2. So if c2 is 2, have another bunch of auto-incrementing LOT numbers starting from 1:</p>
  
  <pre><code>ID  c1  c2  LOT
  6   3   
  7   5   2   1
  8   5   1   4
  9   5   2   2
  10  5   2   3
  </code></pre>
  
  <p>We are using MSSQL 2014 Enterprise Ed. Would table-partitioning be useful or do I need to create special tables for each distinct value of C2?</p>