Is it possible in SQL Server to create a function which could handle a sequence?

We are looking at various options in porting our persistence layer from Oracle to another database and one that we are looking at is MS SQL. However we use Oracle sequences throughout the code and because of this it seems moving will be a headache. I understand about @identity but that would be a massive overhaul of the persistence code.

Is it possible in SQL Server to create a function which could handle a sequence?


That depends on your current use of sequences in Oracle. Typically a sequence is read in the Insert trigger.

From your question I guess that it is the persistence layer that generates the sequence before inserting into the database (including the new pk)

In MSSQL, you can combine SQL statements with ';', so to retrieve the identity column of the newly created record, use INSERT INTO ... ; SELECT SCOPE_IDENTITY()

Thus the command to insert a record return a recordset with a single row and a single column containing the value of the identity column.

You can of course turn this approach around, and create Sequence tables (similar to the dual table in Oracle), in something like this:

INSERT INTO SequenceTable (dummy) VALUES ('X');
INSERT INTO RealTable (ID, datacolumns) VALUES (@ID, @data1, @data2, ...)

I did this last year on a project. Basically, I just created a table with the name of the sequence, current value, & increment amount.

Then I created a 4 procs :

  • GetCurrentSequence( sequenceName)
  • GetNextSequence( sequenceName)
  • CreateSequence( sequenceName, startValue, incrementAmount)
  • DeleteSequence( sequenceName)

But there is a limitation you may not appreciate; functions cannot have side effects. So you could create a function for GetCurrentSequence(...), but GetNextSequence(...) would need to be a proc, since you will probably want to increment the current sequence value. However, if it's a proc, you won't be able to use it directly in your insert statements.

So instead of

insert into mytable(id, ....) values( GetNextSequence('MySequence'), ....);

Instead you will need to break it up over 2 lines;

declare @newID int;
exec @newID = GetNextSequence 'MySequence';
insert into mytable(id, ....) values(@newID, ....);

Also, SQL Server doesn't have any mechanism that can do something like




Hopefully, somebody will tell me I am incorrect with the above limitations, but I'm pretty sure they are accurate.

Good luck.

Need Your Help

PHP PDO prepared insert - does not insert data and show no errors

php mysql insert pdo

This problem is driving me crazy, i tried everything. Is does not give me any error, but it does not insert anything to the database either. Database connection is good, and there should be no typos.