Search code examples
concurrencysql-server-2012auto-incrementdatabase-trigger

Automate sequential integer IDs without using Identity Specification?

Are there any tried/true methods of managing your own sequential integer field w/o using SQL Server's built in Identity Specification? I'm thinking this has to have been done many times over and my google skills are just failing me tonight.

My first thought is to use a separate table to manage the IDs and use a trigger on the target table to manage setting the ID. Concurrency issues are obviously important, but insert performance is not critical in this case.

And here are some gotchas I know I need to look out for:

  • Need to make sure the same ID isn't doled out more than once when multiple processes run simultaneously.
  • Need to make sure any solution to 1) doesn't cause deadlocks
  • Need to make sure the trigger works properly when multiple records are inserted in a single statement; not only for one record at a time.
  • Need to make sure the trigger only sets the ID when it is not already specified.

The reason for the last bullet point (and the whole reason I want to do this without an Identity Specification field in the first place) is because I want to seed multiple environments at different starting points and I want to be able to copy data between each of them so that the ID for a given record remains the same between environments (and I have to use integers; I cannot use GUIDs).

(Also yes, I could set identity insert on/off to copy data and still use a regular Identity Specification field but then it reseeds it after every insert. I could then use DBCC CHECKIDENT to reseed it back to where it was, but I feel the risk with this solution is too great. It only takes one time for someone to make a mistake and then when we realize it, it would be a real pain to repair the data... probably enough pain that it would have made more sense just to do what I'm doing now in the first place).

Solution

  • SQL Server 2012 introduced the concept of a SEQUENCE database object - something like an "identity" column, but separate from a table.

    You can create and use sequence from your code, you can use the values in various place, and more.

    See these links for more information: