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?

Answer 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:

Answer 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

Answer 3

Reference:https://learn.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

Answer 4

The 'newid()' method unique id generate for per record.

AddColumn("dbo.Foo", "Key", c => c.String(nullable: false, maxLength: 250, defaultValueSql: "newid()"));

Answer 5

If you want to add the id manually you can use,

PadLeft() or String.Format() method.

string id;
char x='0';
id=id.PadLeft(6, x);
//Six character string id with left 0s e.g 000012

int id;
id=String.Format("{0:000000}",id);
//Integer length of 6 with the id. e.g 000012

Then you can append this with UID.

Answer 6

Table Creating

create table emp(eno int identity(100001,1),ename varchar(50))

Values inserting

insert into emp(ename)values('narendra'),('ajay'),('anil'),('raju')

Select Table

select * from emp

Output

eno     ename
100001  narendra
100002  rama
100003  ajay
100004  anil
100005  raju