CODEWITHSUNDEEP
sql-serveruniqueidentifier
Daniel Powell
Daniel Powell

Reputation: 8283

generating a reliable system wide unique identifier

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.

Upvotes: 0

Views: 312

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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)

Upvotes: 2

jeroenh
jeroenh

Reputation: 26772

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)

Upvotes: 1

Ofer Zelig
Ofer Zelig

Reputation: 17470

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.

Upvotes: 1

Related Questions