3

I'm developing some software that will be used in multiple instances across the country. Like much software that uses logins, I need a unique ID for each user. Each instance of the software needs to operate completely independent, but the chances are high that eventually a few of the databases will be combined. In this case, I would like the ID for each user to be unique across all servers.

Without communication between the servers (They only serve LANs), I've thought that maybe generating an ID from a timestamp accurate to milliseconds could work. With a userpool of only thousands and not millions, the odds of one user being created at the same millisecond as another user on another server are pretty low.

Is there really any way to guarantee a unique ID across all servers without communication between them?

Indigenuity
  • 9,332
  • 6
  • 39
  • 68

4 Answers4

6

Use the 16-byte uniqueidentifier data type

An example would be

SELECT NEWID()
GO
-- This will return a new random uniqueidentifier e.g.
E75B92A3-3299-4407-A913-C5CA196B3CAB

To select this Guid in in a variable

--assign uniqueidentifier in a variable
DECLARE @EmployeeID uniqueidentifier
SET @EmployeeID = NEWID()
You can directly use this with INSERT statement to insert new row in table.

-- Inserting data in Employees table.

INSERT INTO Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID(), 'John Kris', '99-99999')

examples were from here, if you want more info

Doozer Blake
  • 7,677
  • 2
  • 29
  • 40
jdross
  • 1,188
  • 2
  • 11
  • 30
5

Yes, you can do this quite easily by using auto incrementing ID's that increment by multiples of the number of servers you have, but starting from a different number.

For example, if you have 3 databases:

Server 1: IDs increment by 3 starting from 1 E.g. 1, 4, 7, 10
Server 2: IDs increment by 3 starting from 2 E.g. 2, 5, 8, 11
Server 3: IDs increment by 3 starting from 3 E.g. 3, 6, 9, 12
BG100
  • 4,481
  • 2
  • 37
  • 64
  • 2
    Different, but clever idea. You would have to know how many instances you'd have installed to fill in the 3 though, and they would be fixed. – Doozer Blake Oct 27 '11 at 13:42
  • 2
    +1 because it's an interesting idea, but -1 because it's EXTREMELY limited in the number of implementations – JNK Oct 27 '11 at 13:42
  • @Doozer: Yes, but you can just pick a high enough multiplier like 20, which will allow for up to 20 servers... you don't have to use all ID's. – BG100 Oct 27 '11 at 13:43
  • 2
    @BG100 Yep, I get that. At some point you could run into a scale issue. Guess it all depends on what the OP means by multiple instances. – Doozer Blake Oct 27 '11 at 13:44
  • @BG100 - If there wasn't such a thing as a UUID, this would have merit. As it is, there's no practical reason to do this and possibly paint yourself into a corner down the road. – Brian Roach Oct 27 '11 at 13:46
  • @Brian: yes, I agree that UUID would probably be best in most situations, but there are reasons why simple integer ID's are preferable. Without knowing more about the OP's database it's hard to tell which method will work best. – BG100 Oct 27 '11 at 13:49
  • I will probably end up using UUID, but I do agree, a simple ID would be very nice. Another thought I had was use some variable from installing the software(like location) and adding that to the end of an incremental value on each server. Thanks for the idea! – Indigenuity Oct 27 '11 at 13:54
  • 1
    @BrianRoach: "paint yourself into a corner down the road" -- I'm having trouble picturing that ;) – onedaywhen Oct 27 '11 at 13:58
  • @onedaywhen - Well lets see, if I pick "20" then 3 years from now need to add the 21st server, I'd say I'm pretty well into that corner. I don't have that problem if I simply use a UUID. – Brian Roach Oct 27 '11 at 14:10
  • @BrianRoach: but in those 20 years it's likely the road will need to be resurfaced and what if the paint is anyway just chevrons? – onedaywhen Oct 27 '11 at 14:22
  • This approach will not work and should not be recommended because you are limited to only 3 sequences, for example, if you use 4 your sequence will be 4, 7, 10; but the sequence for 1 is already 1, 4, 7, 10. This approach can only work if you the sequences starting at 1, 2, and 3. The maximum number of values would be Int32 / 3. – Parmenion Oct 27 '11 at 15:17
4

Have you tried the uniqueidentifier (GUID) field type?

Doozer Blake
  • 7,677
  • 2
  • 29
  • 40
2

UUID (GUID) is best for your case

cyber_terminator
  • 61
  • 2