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?
- 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];
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.