Joseph Michael Pesch
VP Programming

SQL Server Identity Columns vs. Oracle Sequence Numbers

by 12. August 2008 21:28

In SQL server you create an identity column like the sample below which creates an identity column with seed value of 1 and increment value of 1.  Then when you insert records the column value is incremented and set automatically.

SQL Server Sample:
create table #MyTable(RecID int identity(1,1), MyData varchar(50))
insert into #MyTable(MyData) values('This is a test')
insert into #MyTable(MyData) values('This is another test')
select * from #MyTable
drop table #MyTable

Not so in Oracle.  There you create the integer column; however, you must manually increment and set the column value as part of your insert statement.  You can use a Sequence object (after you create one) as shown below.

create sequence MySequence minvalue 1 maxvalue 999999999999 start with 1 increment by 1 cache 20
create table imdw.MyTable(RecID int, MyData varchar2(50))
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is a test')
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is another test')
select * from imdw.MyTable
drop table imdw.MyTable
drop sequence MySequence

In both cases you should get the following result set:

 RecID MyData 
 1 This is a test 
 2 This is another test

Tags:

Oracle | SQL Server

Comments are closed