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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

[sql] How to automatically generate unique id in SQL like UID12345678?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2008-r2

Examples related to sql-server-2012