About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Friday, October 31, 2014

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