Migrating Oracle Sequences to SQL Server 2008

In Oracle, a sequence object (CREATE SEQUENCE) is used to generate a sequence of unique integer values.

The main purpose of sequences:

• Generate primary keys for tables (ID values)
• Specific use of sequences not related to table IDs, or sharing a single sequence among multiple tables

If sequences are used only to generate ID values for primary columns, the best conversion option is to use IDENTITY columns in SQL Server.

This is the fastest and the most scalable way to generate IDs, it does not have concurrency issues and overhead as other solutions, does not require creating and managing additional tables and functions.

Assume that you have the following table and sequence definitions in Oracle:

   CREATE SEQUENCE seq_states START WITH 1 INCREMENT BY 1;
 
   CREATE TABLE states 
   (
      id NUMBER(7, 0),
      name VARCHAR2(30)
   );

Then there are 2 typical ways to get the next value using NEXTVAL pseudo-column:

    DECLARE
       v_id NUMBER(7,0);
    BEGIN
       -- 1. Firstly assign a new value to a variable, and use the variable in INSERT
       -- This approach often used to avoid subsequent call to CURRVAL
 
       SELECT seq_states.NEXTVAL INTO v_id FROM dual;
       INSERT INTO states VALUES(v_id, 'Texas');
 
      -- 2. Use NEXTVAl directly in INSERT statement
      -- You can use CURRVAL to obtain the assigned sequence value 
 
      INSERT INTO states VALUES(seq_states.NEXTVAL, 'Washington'); 
 
      SELECT seq_states.CURRVAL INTO v_id FROM dual;
      DBMS_OUTPUT.PUT_LINE(v_id);
    END;
    /

In SQL Server, when you create a table you can specify IDENTITY property for a column, the start value and increment:

   CREATE TABLE states 
   (
      id INT IDENTITY(1, 1),
      name VARCHAR(30)
   );

Then when you insert new rows, you can skip id column, its values will be assigned automatically. To retrieve the last inserted id value, you can use SCOPE_IDENTITY() function:

   INSERT INTO states (name) VALUES ('Texas');   
   INSERT INTO states (name) VALUES ('Washington');   
 
   PRINT SCOPE_IDENTITY()

There are certain reasons when you need a sequence generator and you cannot use the IDENTITY property.

For example, you may need to assign consecutive numbers to multiple objects, have multiple independent sequences in a single table, or avoid gaps (Oracle sequences allow gaps, so it is rather an additional feature, not exact emulation) etc.

There are multiple possible implementations but all share one concept:

• Use a table to store the last ID
• Have a procedure incrementing the ID and returning the last value

We can still use IDENTITY property to emulate sequences, but now we assign it to the table that stores IDs, not to the table where data rows are inserted.

     CREATE TABLE t_seq_state 
     (
        id INT NOT NULL IDENTITY(1,1)
     );

Then you can use the following procedure to return the new ID:

   CREATE PROCEDURE sp_get_nextval @sname VARCHAR(30), @nextval INT OUTPUT
   AS
      EXEC ('INSERT INTO ' + @sname + ' DEFAULT VALUES');
      SET @nextval = @@IDENTITY
   GO

Note. If this example, we use dynamic SQL to insert values, so we have to use @@IDENTITY to get the last ID as SCOPE_IDENTITY() will return NULL.

This solution inserts a new row to the additional table and allows gaps, and at first glance, it does not look as a good solution, but it has certain benefits.

This solution is scalable, multiple concurrent transactions can get their IDs from the same sequence without locking each other. This approach better suits for long running transactions.

You can create a database job to clear row in the sequence table. Actually you can truncate table (remove all rows) at any time, because this does not affect on identity counter.

Since we do not need any rows in the sequence table, you can slightly modify the procedure to roll back inserted row after insert.

   CREATE PROCEDURE sp_get_nextval @sname VARCHAR(30), @nextval INT OUTPUT
   AS
   SAVE TRANSACTION getnext
       EXEC ('INSERT INTO ' + @sname + ' DEFAULT VALUES');
       SET @nextval = @@IDENTITY;
   ROLLBACK TRANSACTION getnext
   GO

Concurrent Use

Let's run 2 instances of SQL Server Management Studio and open 2 connections to the same database.

To get next ID, we will use the following Transact/SQL code:

   BEGIN 
    DECLARE @value INT; 
    EXECUTE sp_get_nextval 't_seq_state', @value OUTPUT 
    PRINT @value 
   END 
   GO

Then run:

You can see that even if we use a procedure that rolls back inserted rows to t_set_state table, 2 transactions do not lock each other.

We can have a table that stores the sequence name, the last ID value and increment:

     CREATE TABLE t_sequences 
     (
        name VARCHAR(30) NOT NULL,
        id INT NOT NULL,
        inc INT NOT NULL DEFAULT 1
     );
 
     INSERT INTO t_sequences VALUES ('t_seq_state', 1, 1);

So to get the next ID we need to perform 2 operations: read the current value, and update the table to set the new value.

We cannot use separate SELECT and then UPDATE statements, because concurrent transactions can run SELECT simultaneously and get the same ID value (HOLDLOCK hint does not block other transaction from reading the data).

Fortunately, SQL Server provides the specialized UPDATE syntax that allows updating a column value and assigning the result value to a variable in the single statement.

You can use the following procedure to return the new ID:

   CREATE PROCEDURE sp_get_nextval @name VARCHAR(30), @nextval INT OUTPUT
   AS
     UPDATE t_sequences SET @nextval = id = id + inc WHERE @name = name
   GO

This solution requires minimal storage space, but it allows gaps in IDs and only one transaction can get ID for the same sequence at a time, other transactions have to wait until the last transaction that got ID completes.

Concurrent Use

Again let's run 2 instances of SQL Server Management Studio and open 2 connections to the same database.

To get next ID, we will use the following Transact/SQL code:

   BEGIN 
    DECLARE @value INT; 
    EXECUTE sp_get_nextval 't_seq_state', @value OUTPUT 
    PRINT @value 
   END 
   GO

Then run:

You can see that 2 transactions trying to get ID for the same sequence lock each other.

• T-SQL Puzzle Solution – Custom Sequence by Itzik Ben-Gan (SQL Server Magazine)
• Migration of Oracle sequences to SQL Server 2005/ 2008 (DBBest blog, developer of SSMA)

• SQL Server White Papers, Articles and Reference
• Oracle White Papers, Articles and Reference