Create multiple auto generated column in SQL server table

We can have multiple auto-generated columns in a SQL Server table, and this is very common in real-time enterprise systems like billing, insurance, logistics, banking, ecommerce, etc.

There is multiple way to generate multiple auto-generated ids.

Ø  Identity and Sequence

Ø  Multiple Sequence

Ø  Auto GUID and Identity

Ø  Computed Column

Ø  Default columns

we can only one identify key will create in a table. Identity key is a auto generated key. If we create multiple it will throw error.

create table demotbl (id int identity(1,1), insert_id int identity(1,1) )

 It will throw an error.

Here we are creating a table with all above combination.

Creating sequence

CREATE SEQUENCE Multi_Auto_Seq     AS BIGINT     START WITH 100000     INCREMENT BY 1     CACHE 50;

Now creating table

CREATE TABLE multi_auto_demo   (      localorderid INT IDENTITY(1, 1) PRIMARY KEY,-- Auto 1      orderid      BIGINT DEFAULT next value FOR multi_auto_seq,-- Auto 2      localtrackingnumber AS ( 'LTRK-'           + Cast(localorderid AS VARCHAR(20)) ),      --Auto 3 -complted column from identity comuln      trackingnumber AS ( 'TRK-' + Cast(orderid AS VARCHAR(20)) ),      -- Auto 4 --complted column from Sequence      customername VARCHAR(100),      amount       DECIMAL(12, 2),      createddate  DATETIME DEFAULT Getdate(),-- Auto 5      updateddate  DATETIME DEFAULT Getdate(),-- Auto 6      uniqueref    UNIQUEIDENTIFIER DEFAULT Newid() -- Auto 7   );

Table created successfully.

Now inserting few records.

INSERT INTO multi_auto_demo (CustomerName, Amount) VALUES ('Bagesh', 55000.75),        ('Rajesh', 43000.25),        ('Mahesh', 32000.00);

Record inserted successfully.

Now see the data

In this way we can created multiple auto generated column in a table.