In Oracle, you can use a combination of a trigger and sequence to generate unique values for the key columns. However, when migrating your Oracle database to Microsoft SQL Server, you need to find the right path to convert this construction. This is an architecture-level decision, so you need to approach it at the very beginning of your migration project. Otherwise, you will need to recreate tables, reload data and recreate backups, and even rewrite code that relates to these tables.
Original Oracle code
Let us consider the following example. The source Oracle script looks as follows:
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE TRIGGER TEST.TR_TEST BEFORE INSERT ON TEST.T_TEST REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF :NEW.SEQ_ID IS NULL THEN SELECT TEST.S_TEST.NEXTVAL INTO :NEW.SEQ_ID FROM DUAL; END IF; END; |
The solution
You can use 2 following approaches to the conversion of the trigger and sequence:
- create IDENTITY columns
- convert the source sequence to SQL Server sequence
If the source Oracle’s sequence is used only for ID generation, we suggest leveraging IDENTITY columns in SQL Server. This is the fastest way to generate IDs. In this case, you will not face concurrency issues. Also, you will not need to create and maintain additional objects.
1 2 3 4 5 | CREATE TABLE TEST.dbo.T_TEST (SEQ_ID BIGINT IDENTITY (1,1), TEST_NAME VARCHAR(100), ...) GO |
Use caution if you use INSERT statements in the code against the tables with an IDENTITY column. Be sure to either to exclude this IDENTITY column from the insert list or SET IDENTITY_INSERT option to ON.
Also, after INSERT don’t forget to immediately SET IDENTITY_INSERT to OFF. The reason for that is that at any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
Alternative solution
However, before you decide to remove a sequence, you need to make sure that this sequence is used only in the trigger. Otherwise, if the source sequence is used for different purposes, we suggest leveraging SQL Server sequences. This is the case, for example, if your sequence is shared among multiple tables. Our experience shows that SQL Server sequences almost fully cover the functionality of Oracle sequences.
