Blog: How to Emulate Oracle Sequences in SQL Server

When migrating Oracle tables to SQL Server, the most common problem you will face is sequence conversion. SQL Server 2008 does not have any object even distantly resembling an Oracle sequence, and some trade-offs are inevitable when you make decisions about the mechanisms to implement its behavior in the target system. In SQL Server 2012 Microsoft introduced sequences, but several incompatibilities with Oracle implementation still exist.

Oracle Sequences in SQL Server

First, let us discuss what you can do if the target system is SQL Server 2008.

Sequences in Oracle

In Oracle, a sequence is an independent database object whose main purpose is automatic generation of unique integers for primary key values. Usually each call to NEXTVAL function increments the sequence value. This value is transaction-independent, because it does not return to the previous state when a user transaction rolls back. Also, Oracle handles this value as a global item within a schema.

Oracle ensures that a sequence is incremented independently by parallel sessions. This means that each session can get its own current value by a call to CURRVAL function. This function returns the last NEXTVAL value generated within the current session.  Commonly used parameters for a sequence are starting number and the step of increment. In other cases, the CYCLE functionality might be used, when the value is reset to minimum value after it reaches a specified maximum value. Note that the sequence step can be negative so the series of generated numbers become descending.

A sequence, which generates primary keys, and the table, which consumes the sequence values, are two completely independent Oracle objects. There is no restriction about the integration ways. The most common method for loading sequence values into table is call to NEXTVAL in INSERT statement, or a BEFORE trigger modifying the key in :NEW record.

SQL Server alternatives

We don’t have a similar feature like Oracle’s NEXTVAL in SQL Server 2008. When it is necessary to produce primary keys for a table, we usually create a numeric column and define it as IDENTITY. A SQL Server IDENTITY becomes a property of the table. An IDENTITY value cannot be set explicitly as a part of an INSERT statement unless a special SET IDENTITY_INSERT mode is set for the table. In no way an IDENTITY could ever be explicitly updated. During creation, the integer start value and increment should be specified. It is possible to create descending IDENTITY by using a negative increment, but any functionality similar to Oracle’s CYCLE is not available. The IDENTITY value cannot be reset unless the table is truncated, or a highly-privileged command DBCC CHECKIDENT is executed.

There are two methods of solving the problem, each one having its own pros and cons.

I. Emulation

First method is to use automatic conversion by SQL Server Migration Assistant v5.2 (SSMA):

https://www.microsoft.com/en-us/download/details.aspx?id=54258

When converting a source schema SSMA generates two additional objects in the target SQL Server schema for each sequence <seq>:

  • Table named $SSMA_seq_<seq>, which contains a single identity column.
  • Stored procedure $$SSMA_sp_get_nextval_<seq>.

The point of this solution is that the stored procedure inserts default row into the table and rolls this operation back immediately. After that, IDENTITY for the table gets incremented and we can use this new value later in T-SQL code. Rollback is necessary since we don’t want to allow unrestricted growth of the sequence emulation table. Original NEXTVAL and CURRVAL calls are replaced with calls to special procedures and functions from SSMA sysdb database.

Pros and Cons of Emulation

The great advantage of this approach is that SSMA does all of the emulation work automatically. Moreover, the emulated code covers most of common Oracle sequence usage. Like in Oracle, the “sequence” remains transaction independent. Also, SSMA is able to set the current value properly so that the key generation in SQL Server can resume exactly from the value where Oracle server has been positioned just before the migration is started.

Still this emulation is not simple and might create a serious negative impact on performance. Particularly, when you call NEXTVAL emulation from a user-defined function. SSMA runtime spawns a new session so that the process can go outside the function context and becomes able to perform an INSERT command. The overhead is not that big if NEXTVAL is called from a stored procedure. Note that it takes some time for sysdb code to find the SSMA-generated target procedure that it should invoke.

Note also that some sysdb modules of SSMA rely on SQL Server Extended Procedure interface, and they contain unmanaged code. This interface is already marked as obsolete and may be discontinued in the next releases of SQL Server.

Time matters

Generally, the speed of SSMA emulation in case of NEXTVAL is about 15-20 times less than the original Oracle speed. However, there is a workaround for the performance problem. T-SQL code might get the next sequence value directly from $SSMA_sp_get_nextval_stored procedure. This reduces the overhead so that the emulation T-SQL code runs only 10 times slower than the original NEXTVAL. Also, in this case you don’t need to call any of the obsolete extended procedures. This requires some minor manual intervention into SSMA generated code, but the effort is reasonably small.

Just keep in mind the following: as some of the housekeeping operations performed by SSMA sysdb modules are now left out, the scope of this solution becomes more limited. Namely, in this case

  • calls of CURRVAL emulation become not available;
  • $SSMA_seq_ table is no more purged and can grow unlimitedly. If the keys for a sequence are generated frequently, you should think of an additional process for cleaning up the table;
  • you cannot call a stored procedure from SQL Server user-defined function context.

II. Adaptation

The second approach to the conversion is to completely forget about Oracle functionality and move to SQL Server native IDENTITY solution.

SSMA can also help in this way of conversion, but you need to perform some setup actions for that. First, you should open Tools > Project Settings > General screen and change option Convert Sequence Generator to “Using SSMA sequence generator”. Now SSMA knows that you want to use identities instead of emulated sequences. But still SSMA cannot figure out what exact table columns will be involved. As we discussed above, that’s because Oracle does not store information about sequence to column mapping in its metadata.

You should provide this mapping manually, and that might be the hard part of this task. Go to a table on the source metabase tree and find new Sequence column on the right-hand pane. If the table has a primary key, you will see a ‘…’ button in this column. Click on it and choose the sequence which will be linked to this column. After you choose the sequence, SSMA creates proper IDENTITY column on target. After that, SSMA even process some common cases of sequence-to-table linking in PL/SQL code. The primary key column will be removed from INSERT statements where the value is supplied by NEXTVAL. Also, SSMA comments out the following code in BEFORE trigger as redundant:

SELECT .NEXTVAL INTO :NEW.id FROM DUAL

Pros and cons of this approach

SSMA doesn’t handle more sophisticated ways of sequence usage. These include:

  • Simultaneous inserting of the same primary key into two or more columns
  • Conditional insert NEXTVAL of primary key, for example when explicit key was not supplied
  • CYCLE functionality

This approach does not require creating of any additional objects and it is the best for performance. That’s because SQL Server generates the keys as fast as Oracle sequences, or even faster.

Note that this approach also breaks all links with Oracle-like sequence mechanisms, which remained in an emulated form if we applied the first conversion method. That has some important consequences. For example, you will not be able to use any kind of “CURRVAL” call. SQL Server supplies functions SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY, but none of them is the exact equivalent for CURRVAL, so you should decide for each case which one of them can be selected.

III. Upgrade your SQL Server

Finally, good news: if we choose SQL Server 2012 as the target platform, all the above solutions might be not necessary. In this new version Microsoft developers added CREATE SEQUENCE command

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

which supports most of the parameters of Oracle sequences. You can receive the next value for SQL Server sequences from NEXT VALUE FOR function. It works as fast as Oracle NEXTVAL. In addition, SSMA since v5.1 has been able to automatically Oracle sequences to SQL Server using this new feature. You can find explanations about this type of conversion here.

The only serious problem left is emulation of CURRVAL, which still is missing in SQL Server. If migrated database or application contains many references to CURRVAL, the only solution will be to fall back on the older SSMA conversion algorithm by changing Convert Sequence Generator setting.