Learn / SQL Server → Oracle / Identity & Sequences
Lesson 3 of 5

Identity & Sequences

SQL Server's traditional approach bakes the counter directly into the column with IDENTITY. Classic Oracle treats "generate the next primary key value" as a separate concern from the table itself: you create a standalone SEQUENCE object and pull from it explicitly. Both approaches now exist in both databases, but the traditional-and-most-common pattern on each side is different enough to trip people up.

💡 The Bottom Line

SQL Server's classic pattern: mark the column itself IDENTITY(1,1) and just omit it from your INSERT column list — the engine fills it in. Classic Oracle's pattern: create a SEQUENCE, then reference sequence_name.NEXTVAL explicitly in your INSERT. To retrieve the value you just generated, SQL Server uses SCOPE_IDENTITY(); Oracle uses CURRVAL (or the RETURNING clause).

Generating primary keys

SQL Server (what you know)
CREATE TABLE employees (
  employee_id INT IDENTITY(1,1) PRIMARY KEY,
  first_name  VARCHAR(50)
);

INSERT INTO employees (first_name)
VALUES ('Ada');

SELECT SCOPE_IDENTITY();
Oracle (the new equivalent)
CREATE SEQUENCE emp_seq
  START WITH 1
  INCREMENT BY 1;

INSERT INTO employees (employee_id, first_name)
VALUES (emp_seq.NEXTVAL, 'Ada');

SELECT emp_seq.CURRVAL FROM DUAL;

📋 Key differences

  • IDENTITY is a property of one specific column on one specific table; it can't be shared. A classic Oracle SEQUENCE is a free-standing object, decoupled from any table — you can share one sequence across several tables, or generate values without inserting anywhere.
  • With IDENTITY, you never reference the counter in the INSERT at all — you just leave the column out of the column list and SQL Server assigns it. NEXTVAL both advances and returns the sequence's counter; in Oracle you call it inline inside the INSERT yourself, on every insert, or the row gets no value at all for that column.
  • SCOPE_IDENTITY() reads the last identity value generated by an insert in the current scope (same stored procedure/batch). Oracle's CURRVAL reads the last value your session generated from a specific named sequence — closely analogous, but scoped to a named sequence object rather than to execution context.
  • SQL Server also has @@IDENTITY and IDENT_CURRENT('table') for wider scopes — Oracle has no exact equivalents, since CURRVAL is always tied to one specific sequence name rather than to "whatever table was last inserted into."

⚠️ Gotcha

Calling emp_seq.CURRVAL before you've called emp_seq.NEXTVAL at least once in your current session raises ORA-08002: sequence NEXTVAL has not been defined in this session. Unlike SQL Server's SCOPE_IDENTITY(), which simply returns NULL if nothing has been inserted yet, Oracle's CURRVAL throws a hard error the very first time — you always need at least one NEXTVAL call in the session before CURRVAL means anything.

The modern bridge: Oracle 12c+ IDENTITY columns

SQL Server (what you know)
CREATE TABLE employees (
  employee_id INT IDENTITY(1,1) PRIMARY KEY,
  first_name  VARCHAR(50)
);

INSERT INTO employees (first_name)
VALUES ('Ada');
Oracle 12c+ (the new equivalent)
CREATE TABLE employees (
  employee_id NUMBER GENERATED ALWAYS
              AS IDENTITY,
  first_name  VARCHAR2(50)
);

INSERT INTO employees (first_name)
VALUES ('Ada');

If your target is Oracle 12c or later, you don't have to give up the IDENTITY workflow you already know: GENERATED ALWAYS AS IDENTITY is a column-level auto-increment that behaves almost exactly like SQL Server's IDENTITY — no separate sequence object to manage, and you just omit the column from your INSERT. Under the hood it's actually implemented using a hidden sequence, but you don't need to touch that sequence directly; it's a genuine bridge concept between the two worlds.

🔧 Try It Yourself

Take a SQL Server table that uses IDENTITY(1,1) and rewrite it two ways for Oracle: once using the classic pattern (a standalone CREATE SEQUENCE plus referencing NEXTVAL in every INSERT), and once using Oracle 12c+'s GENERATED ALWAYS AS IDENTITY. Notice how much closer the second version feels to the SQL Server table you started with.

🙋 There Are No Dumb Questions

Q: If I'm stuck on an older Oracle version without 12c+ identity columns, how did people handle auto-increment before that existed?
A: The classic pre-12c pattern pairs a SEQUENCE with a BEFORE INSERT trigger on the table: the trigger calls :NEW.employee_id := emp_seq.NEXTVAL; whenever the column is left null, so callers don't have to reference the sequence explicitly in every INSERT — much closer to the "just omit the column" experience you're used to from SQL Server. You'll see this sequence-plus-trigger pattern constantly in existing Oracle codebases, even on databases that are new enough to use the simpler 12c+ syntax instead.

✅ Quick Check

You've just run INSERT INTO employees (employee_id, first_name) VALUES (emp_seq.NEXTVAL, 'Ada'); for the first time in a brand-new session, and now want the ID that was just generated. What happens if you call emp_seq.CURRVAL in that same session right after?

Next up: moving business logic across — how T-SQL's stored procedures and schemas compare to PL/SQL's block structure and packages.

← Back to
Next →