How to have identity property set for multiple column in a table

Lets try out with a small demo, here we are creating table with three column, out of which two column has identity property set.

create table dupIdentity

(col1 int identity(1,1),

col2 int identity(1,1),

col3 varchar(10))

Msg 2744, Level 16, State 2, Line 1

Multiple identity columns specified for table 'dupIdentity'. Only one identity column per table is allowed.

Identity property is not allowing me to have on multiple columns.

So what’s the alternative, apparently SQL Server 2012 feature SEQUENCE Object? Lets try out. First we will create table without any identity property column

create table dupIdentity

(col1 int,

col2 int,

col3 varchar(10))

Command(s) completed successfully.

Now lets create two sequence object seq1 and seq1, one which will starts from 1 and another will starts from 10 and incremented by 1 after every insert.

create sequence seq1 as int

start with 1

increment by 1

create sequence seq2 as int

start with 10

increment by 1

Command(s) completed successfully.

Now since we have sequence object in place, lets apply it to our table

insert into dupIdentity values ( next value for seq1, next value for seq2, 'Pragyan')

insert into dupIdentity values ( next value for seq1, next value for seq2, 'Lopamudra')

(2 row(s) affected)

Lets query the table to find the data inserted.

select * from dupIdentity

col1        col2        col3

----------- ----------- ----------

1           10          Pragyan

2           11          Lopamudra

(2 row(s) affected)

That’s it J

Conclusion: we can stimulate multiple identity property through Sequence Object of SQL Server 2012.

Nights are bore to tears when I am far from you, my SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share