title: Which Schema change scenario?
body: <p>My current schema looks like the following:</p>
  
  <pre><code>ID | DisplayVal
  --   ----------
   1     1-H-3
   2     2-H-3
   3     3-J-4
  </code></pre>
  
  <p>In the above, the <code>ID</code> field is an <code>IDENTITY INT</code> field which is also used as and end user <code>account Id</code>.  The <code>DisplayVal</code> is what they see on the screen.</p>
  
  <p>A new client has provided their own <code>Account Id</code> values, but they are <code>alpha-numeric</code>, so they can't just go into the <code>IDENTITY</code> field.  Here are my scenarios:  I am looking for a scenario that would offer the best <code>maintainability</code>, <code>end user experience</code>, <code>magnitude and impact of changes</code> and <code>testing/QA impact</code>.</p>
  
  <p>My first scenario was to add an <code>Account Number</code> column that would be a <code>VARCHAR(x)</code> and accommodate all types of <code>Account Numbers</code>.  It would look like this:</p>
  
  <pre><code>ID | DisplayVal | AccountNumber
  --   ----------   -------------
   1     1-H-3            1
   2     2-H-3            2
   3     3-J-4            3
   4     h389             h389
   5     h-400-x          h400
  </code></pre>
  
  <p>In the above, in the case of the first client, the <code>seeded Identity</code> which is the <code>Account Id</code> would be copied into the <code>Account Number</code>, but for the other client, there would still be a <code>seeded Identity</code> created, but their <code>Account Number</code> would be different and it may or may not match the <code>Display Value</code>.</p>
  
  <p>My second scenario was to not add any columns and for clients that provide an <code>Account Number</code>, I would turn off <code>IDENTITY INSERT</code> and insert the new Id's and then turn identity insert back on.  If a client did not provide an <code>Account Number</code>, I would auto-generate one, obviously trying to avoid collisions.</p>
  
  <p>The third scenario was basically to leave the new <code>Account Number</code> as a <code>legacy Account Number</code> and create new identity values for all new records. This would require the end-user to become familiar with a new <code>Account Number</code>.  It is probably the easiest, but not sure if there are any downsides.</p>
  
  <p>If there is another scenario you know that would work well in this case, let me know.</p>