CODEWITHSUNDEEP
sqluniqueidentifier
Indigenuity
Indigenuity

Reputation: 9760

Unique ID across multiple SQL servers

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?

Upvotes: 3

Views: 2185

Answers (4)

jdross
jdross

Reputation: 1206

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

Upvotes: 6

cyber_terminator
cyber_terminator

Reputation: 61

UUID (GUID) is best for your case

Upvotes: 2

BG100
BG100

Reputation: 4531

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

Upvotes: 5

Doozer Blake
Doozer Blake

Reputation: 7797

Have you tried the uniqueidentifier (GUID) field type?

Upvotes: 4

Related Questions