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?

Solution 1

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:

Solution 2

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

Solution 3

Reference:https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-2017

-- Creating a table using NEWID for uniqueidentifier data type.

CREATE TABLE cust  
(  
 CustomerID uniqueidentifier NOT NULL  
   DEFAULT newid(),  
 Company varchar(30) NOT NULL,  
 ContactName varchar(60) NOT NULL,   
 Address varchar(30) NOT NULL,   
 City varchar(30) NOT NULL,  
 StateProvince varchar(10) NULL,  
 PostalCode varchar(10) NOT NULL,   
 CountryRegion varchar(20) NOT NULL,   
 Telephone varchar(15) NOT NULL,  
 Fax varchar(15) NULL  
);  
GO  
-- Inserting 5 rows into cust table.  
INSERT cust  
(CustomerID, Company, ContactName, Address, City, StateProvince,   
 PostalCode, CountryRegion, Telephone, Fax)  
VALUES  
 (NEWID(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,  
 '90110', 'Finland', '981-443655', '981-443655')  
,(NEWID(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP',  
 '08737-363', 'Brasil', '(14) 555-8122', '')  
,(NEWID(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL,   
 '1010', 'Argentina', '(1) 135-5555', '(1) 135-4892')  
,(NEWID(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,  
 '8010', 'Austria', '7675-3425', '7675-3426')  
,(NEWID(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,  
 'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68');  
GO

Comments

[KumarHarsh]

first id has to be bigint.second how will 12345678 geenerated with your table like this UID0000000012345678 . so it has to be UDF and format 0000... using replicate .

[Nishantha]

thax @marc_s, work without errors. :) Another small issue can 00000000 generate randomly without incrementing??

[Raj]

@KumarHarsh - What do you think UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED does?

[Nishantha]

I have not tried something like that in mysql. But you can ask it from the genius @marc_s

[marc_s]

@MohammedHousseynTaleb: sorry, I don't know MySQL well enough to tell you whether it supports this kind of thing.... but YOU can consult the MySQL documentation and see if it does!

[Elham Kohestani]

marc_s I have a problem. I can't create a foreign key in another table which is referring to UID column of User table because UID doesn't have a data type. How to overcome this?

[marc_s]

@ElhamKohestani: see my update, OF COURSE it has a datatype !!

[Elham Kohestani]

Thank you, got it. But I still have a problem since it is nullable I can't refer it to a foreign key. I tried to modify it but it says Property can not be modified. How can I modify it to prevent from null values?

[Elham Kohestani]

What if I add 'not null' at the end of column during the table creation query ?

[Elham Kohestani]

I tried to alter it but it says: Cannot alter column 'UID' because it is 'COMPUTED'.

[marc_s]

@ElhamKohestani: you need to define it like this, if it has to be not nullable: UserID AS ISNULL('UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8), '*') PERSISTED - with the use of ISNULL(), you can make sure that it's never ever going to be null

[Elham Kohestani]

@marc_s thank you. I already solved that by creating the table from scratch and adding a UNIQUE constraint on the column. Won't it cause any problem in the future?

[marc_s]

@ElhamKohestani: no, since the ID is an identity, that in itself is unique, so any computed column based on that value will be unique, too

[Elham Kohestani]

Thank you @marc_s could you answer my question, please? stackoverflow.com/questions/43298886/…