I have to generate account names instantly. They'll be utilized in user software which will access my service, so they aren't always pretty searching. I suppose any alphanumeric string lengthy enough is going to do. Assume I curently have an formula that creates adequate alphanumeric string.

There are two major needs: they ought to be unique and they'll be produced at the same time. Particularly my service will operate on multiple machines and all sorts of copies will access exactly the same shared database. I have to generate individuals usernames such method in which no two nodes ever generate identical usernames.

How do you do that? Will I just leave this concept and employ GUIDs? It is possible to more attractive method in which GUIDs with this scenario?

Among:

  • Use GUIDs (uniqueidentifier data type) although not like a clustered index
  • Make use of an IDENTITY column

If SQL Server replication can be used over multiple nodes (Edit: was thinking an excessive amount of before)

  • Use IDENTITY posts with ranges set per node (eg -1 to -1000000, 1 to 100000 etc)
  • IDENTITY column along with a NodeID column to split up the IDENTITY values

Each one is 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 wish to test this code for concurrency items you can run UPDATE ...; GO 100000 and UPDATE ...; GO 100 in SSMS in 2 separated home windows/queries and, in the finish, you are able to run this question SELECT UserName, COUNT(*) Num FROM dbo.[User] ORDER BY COUNT(*) DESC to try to find replicates.