How to automatically generate unique id in SQL like UID12345678?

Question

I want to automatically generate unique id with per-defined code attach to it. ex: ``` UID12345678 CUSID5000 ``` I tried `uniqueidentifier` data type but it generate a id which is not suitable for a user id. Any one have suggestions?

Accepted Answer

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:

How to automatically generate unique id in SQL like UID12345678?

Solution

Problem

Related problems