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';
Last Value Across All Sessions	Use SELECT MAX(id) from a user table(s) using the sequence
Alternatives		IDENTITY columns