When migrating Oracle tables to SQL Server, the problem of sequence conversion is one of the most common. SQL Server 2008 does not have any object even distantly resembling an Oracle sequence, and some tradeoffs 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.
First, let us discuss what you can do if the target system is SQL Server 2008.
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, this value is handled as a global item within a schema.
Oracle ensures that a sequence is incremented independently by parallel sessions, while each session can get its own current value by a call to CURRVAL function, which 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 ways they can be integrated. 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.
We don’t have a similar feature 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):
http://www.microsoft.com/en-us/download/details.aspx?id=28766
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.
The great advantage of this approach is that all emulation work is done automatically and most of common Oracle sequence usage is covered. 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, especially when NEXTVAL emulation is called 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.
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, no obsolete extended procedures are called in this case. 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 it still cannot figure out what exact table columns will be involved, because, as we discussed above, 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 the sequence is chosen, SSMA will create proper IDENTITY column on target and 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 the following code in BEFORE trigger will be commented out as redundant:
More sophisticated ways of sequence usage are not handled by SSMA. 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 any additional objects to be created and it is the best for performance, because the SQL Server keys can be generated 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.
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. Next value for SQL Server sequences can be received from NEXT VALUE FOR function, which 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.
