title: Using a trigger to simulate a second identity column in SQL Server 2005
body: <p>I have various reasons for needing to implement, in addition to the identity column PK, a second, concurrency safe, auto-incrementing column in a SQL Server 2005 database.  Being able to have more than one identity column would be ideal, but I'm looking at using a trigger to simulate this as close as possible to the metal.</p>
  
  <p>I believe I have to use a serializable isolation level transaction in the trigger.  Do I go about this like Ii would use such a transaction in a normal SQL query?</p>
  
  <p><em>It is a non-negotiable requirement that the business meaning of the second incrementing column remain separated from the behind the scenes meaning of the first, PK, incrementing column.</em>  </p>
  
  <p>To put things as simply as I can, if I create JobCards '0001', '0002', and '0003', then delete JobCards '0002' and '0003', the next Jobcard I create must have ID '0002', not '0004'.</p>