Unique ID across multiple SQL servers

Question

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?

Answer 1

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

Answer 2

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