title: Save sequence in special table or get last value?
body: <p>Do you have advice or best practice or recommendation about sequence for identifiers?</p>
  
  <p>I work on a database where all the identifier or document numbers are 'complex' sequence. For example the sequence for our invoices are INVCCC-2016-0000 where INV is fixed, CCC is the client reference, 2016 is the year and 0000 is a counter from 1 to 9999. This number must be unique and at this moment we keep it in a columns.</p>
  
  <p>When I create a new invoice I need to check the last number created for this client this year then increment it of one then save my data in my database.</p>
  
  <p>I see two way to do it</p>
  
  <ol>
  <li><p>I create a special table that contain and maintain all last used number for each client. Each time I have a new invoice I check the number in this table, I increment it of one, I use this number to save my invoice then I update the sequence table. 1 READ, 1 INSERT, 1 UPDATE (may be an INSERT in sequence is new)</p>
  
  <pre><code>var keyType = "INV" + ClientPrefix + "-" + Year;
  var keyValue = Context.SequenceTable.SingleOrDefault(y =&gt; y.KeyType == keyType).KeyValue;
  </code></pre></li>
  <li><p>I check the last number in my invoice table, I increment it then I save my invoice. 1 READ, 1 INSERT. I don't need tu update another table and this seems more logic to me. But my database administrator tell me this can create lock or other troubles.</p>
  
  <pre><code>var keyType = "INV" + ClientPrefix + "-" + Year;
  var keyValue = Invoices.Where(y =&gt; y.InvoiceId.StartsWith(keyType)).OrderByDescending(y =&gt; y.InvoiceId).LastOrDefault();
  </code></pre></li>
  </ol>
  
  <p><strong>Note I use SQL server before 2015 version and then before the SEQUENCE feature.</strong> I fear an inconsistency with solution 1. I fear performance issue with solution 2.</p>