CREATE SEQUENCE - Sequences - Oracle to SQL Server Migration

Oracle CREATE SEQUENCE statement creates a sequence object that can be used to automatically generate unique integer numbers (IDs, identity, auto-increment).

Starting from Microsoft SQL Server 2012, you can also use sequences in a SQL Server database as well, although there are some syntax differences in sequence options.

Oracle Example:

   -- Define a table
   CREATE TABLE cities
   (
      id NUMBER(10),
      name VARCHAR2(90)
   );
 
   -- Create a sequence
   CREATE SEQUENCE cities_id START WITH 1 INCREMENT BY 1;
 
   -- Insert a row (id 1 will be assigned)
   INSERT INTO cities VALUES (cities_id.NEXTVAL, 'Paris');      
   -- Retrieve ID (id 1 is returned) - last ID inserted by the current (!) session
   SELECT cities_id.CURRVAL FROM dual;

SQL Server Example (Version 2012 and above):

   -- Define a table
   CREATE TABLE cities
   (
      id INT,
      name VARCHAR(90)
   );
 
   -- Create a sequence
   CREATE SEQUENCE cities_id START WITH 1 INCREMENT BY 1;
 
   -- Insert a row (id 1 will be assigned)
   INSERT INTO cities VALUES (NEXT VALUE FOR cities_id, 'Paris');  
   -- Retrieve ID (id 1 is returned) - last ID across all (!) sessions
   SELECT current_value FROM sys.sequences WHERE name = 'cities_id';

Important Note. When you migrate a sequence from Oracle to SQL Server, and the Oracle database contains data, you need to set the start value in SQL Server to the last sequence value in Oracle.

Sequence Migration Overview

Oracle sequence to SQL Server conversion summary:

Oracle SQL Server
Syntax CREATE SEQUENCE seqname
[START WITH start] [INCREMENT BY inc]
[MAXVALUE max] [CYCLE] (full...)
CREATE SEQUENCE seqname
[AS datatype]
[START WITH start] [INCREMENT BY inc]
[MAXVALUE max] [CYCLE] (full...)
Data Type NUMBER(28) Any integer data type
Start Value START WITH start START WITH start
Increment INCREMENT BY inc INCREMENT BY inc
Maximum value MAXVALUE max | NOMAXVALUE MAXVALUE max | NO MAXVALUE (space after NO)
Minimum value MINVALUE min | NOMINVALUE MINVALUE min | NO MINVALUE
Cycling CYCLE | NOCYCLE CYCLE | NO CYCLE
Cache CACHE value |NOCACHE CACHE value | NO CACHE
Order ORDER | NOORDER
Use in DEFAULT
Get Next Value seqname.NEXTVAL NEXT VALUE FOR seqname
Last Value in Current Session seqname.CURRVAL Use a variable
Last Value Across All Sessions SELECT last_number
FROM user_sequences
WHERE sequence_name = 'SEQNAME'
SELECT current_value
FROM sys.sequences
WHERE name = 'seqname';
Use SELECT MAX(id) from a user table(s) using the sequence
Alternatives IDENTITY columns

For migration of Oracle sequences to SQL Server 2008 and earlier, see Migrating Oracle Sequences to SQL Server 2008.

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Resources

Oracle 11g R2 SQL Language Reference

Microsoft SQL Server 2012 - Books Online

SSMA Blog

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - May 2012

You could leave a comment if you were logged in.