How to automatically generate unique id in SQL like UID12345678?
The only viable solution in my opinion is to use
- an
ID INT IDENTITY(1,1)
column to get SQL Server to handle the automatic increment of your numeric value - a computed, persisted column to convert that numeric value to the value you need
So try this:
CREATE TABLE dbo.tblUsers
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
.... your other columns here....
)
Now, every time you insert a row into tblUsers
without specifying values for ID
or UserID
:
INSERT INTO dbo.tblUsersCol1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)
then SQL Server will automatically and safely increase your ID
value, and UserID
will contain values like UID00000001
, UID00000002
,...... and so on - automatically, safely, reliably, no duplicates.
Update: the column UserID
is computed - but it still OF COURSE has a data type, as a quick peek into the Object Explorer reveals:
CREATE TABLE dbo.tblUsers
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
[Name] VARCHAR(50) NOT NULL,
)
marc_s's Answer Snap