How do I generate unique usernames on a shared database concurrently?

I need to generate account names automatically. They will be used in user software that will access my 开发者_如何学Pythonservice, so they are not necessarily pretty looking. I guess any alphanumeric string long enough will do. Assume I already have an algorithm that produces good enough alphanumeric string.

There're two major requirements: they must be unique and they will be generated concurrently. Specifically my service will run on multiple machines and all copies will access the same shared database. I need to generate those usernames in such way that no two nodes ever generate identical usernames.

How do I do this? Do I just leave this idea and use GUIDs? Is there a prettier way that GUIDs for this scenario?

---

One of:

• Use GUIDs (uniqueidentifier data type) but not as a clustered index
• Use an IDENTITY column

If SQL Server replication is used over multiple nodes (Edit: was thinking too much before)

• Use IDENTITY columns with ranges set per node (eg -1 to -1000000, 1 to 100000 etc)
• IDENTITY column and a NodeID column to separate the IDENTITY values

All are concurrency safe

---

CREATE TABLE dbo.CommonName
(
    CommonNameID INT IDENTITY(0,1) PRIMARY KEY
    ,Name NVARCHAR(50) NOT NULL
    ,LastID INT NOT NULL DEFAULT 0
);
INSERT  dbo.CommonName(Name)
VALUES  
 ('JAMES')  
,('JOHN')   
,('ROBERT') 
,('MICHAEL')    
,('WILLIAM')    
,('DAVID')  
,('RICHARD')    
,('CHARLES')    
,('JOSEPH') 
,('THOMAS');
GO

--Test
CREATE TABLE [User](Id INT IDENTITY(1,1) PRIMARY KEY, UserName NVARCHAR(60));
GO

UPDATE  dbo.CommonName WITH(ROWLOCK)
SET     
        LastID = LastID + 1
OUTPUT  inserted.Name+CAST(inserted.LastID AS NVARCHAR(10)) INTO [User](UserName)
WHERE   CommonNameID = ABS(CHECKSUM(NEWID())) % 10
GO 20 --We need 20 new users

SELECT  *
FROM    [User] u
ORDER BY u.Id;
--End of test
GO

DROP TABLE dbo.CommonName;
DROP TABLE dbo.[User];

Sample output:

Batch execution completed 20 times.
Id          UserName
----------- ------------------------------------------------------------
1           RICHARD1
2           MICHAEL1
3           ROBERT1
4           WILLIAM1
5           ROBERT2
6           JAMES1
7           CHARLES1
8           RICHARD2
9           JOSEPH1
10          THOMAS1
11          ROBERT3
12          MICHAEL2
13          WILLIAM2
14          MICHAEL3
15          THOMAS2
16          THOMAS3
17          WILLIAM3
18          RICHARD3
19          JAMES2
20          RICHARD4

(20 row(s) affected)

If you want to test this code for concurrency issues you can run UPDATE ...; GO 100000 and UPDATE ...; GO 100 in SSMS in two separated windows/queries and, at the end, you can run this query SELECT UserName, COUNT(*) Num FROM dbo.[User] ORDER BY COUNT(*) DESC to see if you can find duplicates.