Automatically increasing unidentification column in sql server

Question

Our database has Non- Identity Column that have a specific meaning. We have a requirement as below

Whenever an insert written to this column, the value must be increased by one.

how to handle this in sql server ?

Thanks for the help.

Answer 1

Well, you can use SEQUENCE , introduced in SQL Server 2012, brings an identifier generation method

To use it in an insert statement, you first need to create a sequence like this, -

 CREATE SEQUENCE dbo.Id_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE 

Now use it in the insert statement, like this:

 INSERT INTO dbo.Test1 ( orderid , custid , empid ) SELECT NEXT VALUE FOR dbo.Id_Sequence, @custid , @empid 

What is it.

Answer 2

Try to create TRIGGER

 CREATE TRIGGER incrementValue ON Test FOR Insert AS Update Test set columnvalue = columnvalue +1 where id in (select id from inserted) GO 

Answer 3

You can load the maximum value of the table and add +1

 SELECT MAX(MyColumn)+1 FROM MyTable 

it is possible to add ISNULL for the first run.

 ISNULL((SELECT MAX(MyColumn)+1 FROM MyTable),0)