generating a reliable system wide unique identifier

Question

We store documents in our database (sql server), the documents are spread across various tables so there is no one table that contains all of them.

I now have a requirement to give all documents a system wide unique id, one that is semi-readable, not like a guid.

I've seen this done before by creating a single table with a single row/column with just a number that gets incremented when a new document is created.

Is this the best way to go about it, how do I ensure that no one reads the current number if someone is about to update and and vice versa?

In this case the number can be something like 001 and auto-increment as required, I'm mainly worried about stopping collisions rather than getting a fancy identifier.

its an existing system and they aren't there already, plus they have different data stored against them that doesnt apply to all document types — Daniel Powell, Oct 02 '12 at 04:55

score 2 · Accepted Answer · answered Oct 02 '12 at 06:41

If you want the single row/column approach, I've used:

declare @MyRef int
update CoreTable set @MyRef = LastRef = LastRef + 1

The update will be safe - each person who executes it will receive a distinct result in @MyRef. This is safer than doing separate read, increment, update.

Table defn:

create table CoreTable (
    X char(1) not null,
    LastRef int not null,
    constraint PK_CoreTable PRIMARY KEY (X),
    constraint CK_CoreTable_X CHECK (X = 'X')
)
insert into CoreTable (X,LastRef) values ('X',0)

score 1 · Answer 2 · answered Oct 02 '12 at 04:55

1

You can use Redis for this. Have a look at this article: http://rediscookbook.org/create_unique_ids.html

Redis is a very fast in-memory NoSQL database, but one with persistence capabilities. You can quickly utilize a Redis instance and use it to create incremental numbers which will be unique.

You can then leverage Redis for other many purposes in your app.

Another suggestion for your inquiry that does not involve installing Redis is to use a single DB row/column as you suggested, while encapsulating it in a transaction. That way you won't run into conflicts.

answered Oct 02 '12 at 04:55

Ofer Zelig

17,068
9
59
93

looks interesting, not sure if I want to go that far though just to get a unique number system-wide – Daniel Powell Oct 02 '12 at 04:56
1

If the transaction takes a long time though, would it be possible for someone to obtain the current index and then when the transaction is completed overwrite it with a duplicate value – Daniel Powell Oct 02 '12 at 05:01
No, you should read the current index, add 1 (or so), write it and then use that number for your newly generated record. All that should be done within a transaction. If another process would try to read the current index, that will also be within a transaction and it won't be able to read the number while the first transaction hasn't committed (the record will be locked). – Ofer Zelig Oct 02 '12 at 05:11

jeroenh · Answer 3 · 2012-10-02T06:12:06.593

One 'classic' approach would indeed be to have a seperate table (e.g. Documents) with (at least) an ID column (int identity). Then, add a foreign key column and constraint to all your existing document tables. This ensures the uniqueness of the document ID over all tables.

Something like this:

CREATE TABLE Documents (Id int identity not null)


ALTER TABLE DocumentTypeOne 
ADD CONSTRAINT (DocumentId) DocumentTypeOne_Documents_FK Documents(Id)

generating a reliable system wide unique identifier

3 Answers3