Take the 2-minute tour ×

My current schema looks like the following:

ID | DisplayVal
--   ----------
 1     1-H-3
 2     2-H-3
 3     3-J-4

In the above, the ID field is an IDENTITY INT field which is also used as and end user account Id. The DisplayVal is what they see on the screen.

A new client has provided their own Account Id values, but they are alpha-numeric, so they can't just go into the IDENTITY field. Here are my scenarios: I am looking for a scenario that would offer the best maintainability, end user experience, magnitude and impact of changes and testing/QA impact.

My first scenario was to add an Account Number column that would be a VARCHAR(x) and accommodate all types of Account Numbers. It would look like this:

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

In the above, in the case of the first client, the seeded Identity which is the Account Id would be copied into the Account Number, but for the other client, there would still be a seeded Identity created, but their Account Number would be different and it may or may not match the Display Value.

My second scenario was to not add any columns and for clients that provide an Account Number, I would turn off IDENTITY INSERT and insert the new Id's and then turn identity insert back on. If a client did not provide an Account Number, I would auto-generate one, obviously trying to avoid collisions.

The third scenario was basically to leave the new Account Number as a legacy Account Number and create new identity values for all new records. This would require the end-user to become familiar with a new Account Number. It is probably the easiest, but not sure if there are any downsides.

If there is another scenario you know that would work well in this case, let me know.

share|improve this question

1 Answer 1

You should not use business keys, like account id, as identity. Create a new id column and populate it either using an autoincrement field or a guid. Your users or other systems that interact with your system should not know/depend on this value.

share|improve this answer

Your Answer


