CODEWITHSUNDEEP
sql-serverdatabaseguid
Jay Pete
Jay Pete

Reputation: 4259

Correlation ID in SQL Server

I want to add a CorrelationId column to a number of tables in my existing SQL Server 2012 database.

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.

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.

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.

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.

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.

What would your suggestions be?

Upvotes: 0

Views: 4023

Answers (2)

Gary Walker
Gary Walker

Reputation: 9134

OK, you are basically asking how to create a bigint (ala identity column but shared across multiple tables).

There are a number of viable methods, the best method depends upon how you use it. The recent sequence feature added in SQL 2012 can be useful, esp. if you are used to Oracle generators, etc.

If you have a number of possible callers, all grabbing single (or few) number of values you can run into serious lock/resource contention. But, if you can persist at least minimal state inside the the callers, the following strategy is quite useful.

Make a ID "block allocation" stored proc. You can pass in the number of id's in the requested block. A caller than makes only very rare allocations could allocate using a blocksize of 1, a heavy use caller could allocate 1000 (or more) with a single call.

ADDED -- sample proc -- not tested

create table SharedID
(
    ID bigint not null
)

create proc GetBlock(@BlockSize int) as
begin

declare @dummy table
(
   ID bigint
)

UPDATE SharedID
SET ID = ID + @blocksize
OUTPUT INSERT.ID
into @dummy

-- or put into output parm if you prefer
select @dummy - @blocksize as IDBlockStart

end

If you allocate a block of 1000, you then satisfy 1000 calls internally from that block before you call the stored proc allocator again. If you program crashes, etc. all you lose is the "unused" ids allocated in that block.

Also, If all of your callers would be usefully assigned blocks of 1000, just use a sequence with an increment of 1000 to allocate blocks of IDs.

Upvotes: 0

jhilden
jhilden

Reputation: 12439

I would use a GUID. You can create indexes on GUIDs just fine (although they will be larger than on BIGINT columns.

The other main advantage of a GUID is that they could be generated by a 3rd party system and still used by your system. If you have a table where you're pulling sequential numbers from then you can't support IDs from other systems.

Upvotes: 4

Related Questions