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

Question

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?

Answer 1

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');
SELECT @ID = SCOPE_IDENTITY();
INSERT INTO RealTable (ID, datacolumns) VALUES (@ID, @data1, @data2, ...)

Answer 2

If you have a lot of code, you're going to want to do a massive overhaul of the code anyway; what works well in Oracle is not always going to work well in MSSQL. If you have a lot of cursors, for instance, while you could convert them line for line to MSSQL, you're not going to get good performance.

In short, this is not an easy undertaking.

Answer 3

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

MySequence.Current

or

MySequence.Next

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

Good luck.