High Scalability Building bigger, faster, more reliable websites.

In my project I've got a bunch of web servers connected to a central SQL box, but they also do a load of crunch work on their own local SQLExpress instances for speed.

To get a set of unique ID's that are system wide, I precomputed a load of 9 char keys:

AAAAAAAAAA
AAAAAAAAAB
AAAAAAAAAC

etc
(in actual fact, I converted from decimal to base 36, and went up the range uaing every 1,000,000th value so the keys look a load more random than that.... for my second server I used

1
1,000,001
2,000,001

for 3rd server:

2
1,000,002
2,000,002

This means every server can have it's own allocation of ID's, or just connect via a webservice to get a key.

The table is simply:

Key (Char 9)
Status (Boolean)
TempGUID

Because I'll never repeat a value, for speed I can keep the table fairly small, say around 100,000 free keys. When keys have been marked as used I can archive them off... when it's low on keys I can generate new ones.

To get the keys at random I just do a very simple:

in code:
TempGUID = newGUID (or whatever it is in ASP.net, i forget now)

in SQL;
SET ROWCOUNT 1
UPDATE Keys SET STATUS=1, TempGUID =@TempGUID where status = 0
SELECT key from keys where TEMPGUID = @TempGUID

Hey presto, a 100% unique key even across a set of sharded servers