Generate customized Auto Increment unique key in SQL Server

Last Reply one year ago By david.ee

Posted one year ago

So im using sqlserver 2012 and i create my webapplications using vb.net. I want to dynamically autoincrease my unique id column with customized figures like 0001, 0002, 003 using vb.net.


Posted one year ago

Hello dharmendr,

I followed the link and auto increment based on last value works fine but there are quite a few concerns about that method in multiuser interfaces.

The main concern is that if concurrent users insert data into the database, the likelyhood of having duplicate auto increment number is high.

So I made more search and found two options, either using a computed persisted column or using a sequence.

I chose using a sequence and implemented as follows:

 

CREATE SEQUENCE seq_test
  AS INT
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  NO MAXVALUE 
  NO CYCLE
  CACHE 10;

And to call the sequence during insert:

  

INSERT INTO mytable
(my_id, firstname)
VALUES
(NEXT VALUE FOR seq_test, 'David');

This works fine for me

Thanks