title: Correlation ID in SQL Server
body: <p>I want to add a CorrelationId column to a number of tables in my existing SQL Server 2012 database.</p>
  
  <p>The CorrelationId is an identifier that indicates a relationship between entities without describing that relationship further. Usually this will mean that the records were created within the same transaction, or somehow relates to the same workflow. The correlation id is also used across boundaries. So it will be used in other systems to relate to the same workflow. A new correlation id should be globally unique across boundaries. This is not intended to replace foreign keys. It is for a more loose relationship than foreign keys describe. More of a temporal relationship if you will.</p>
  
  <p>Anywho. I am considering how I should issue these CorrelationIds. One way could be to just use GUIDs. But I would like to be able to index on them as well. </p>
  
  <p>Another way could be to create a CorrelationId Pool, which is essentially a table with a single row and a single BIG INT column. That number should be the next available number, and when I request that number it would increment the counter. What would a query for this look like if I want to make sure there are no race conditions when I request numbers.</p>
  
  <p>The correlation ids should be "free" in the sense that you just ask for one, it is then handed out and can never be used again. Should you decide not to use it (for example if a transaction is rolled back), you can just throw it away, and don't worry about the fact that it can never be used again.</p>
  
  <p>Even though the CorrelationId will not be considered a Foreign Key, it will be used to join tables in queries when needed. For that reason I think I prefer a BigInt, so if I need it, I can create indexes.</p>
  
  <p>What would your suggestions be? </p>