Learn / Oracle → SQL Server / Sequences & Auto-Numbering
Lesson 3 of 5

Sequences & Auto-Numbering

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. SQL Server's traditional approach bakes the counter directly into the column with IDENTITY. 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

Oracle's classic pattern: create a SEQUENCE, then reference sequence_name.NEXTVAL in your INSERT. 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. To retrieve the value you just generated, Oracle uses CURRVAL (or the RETURNING clause); SQL Server uses SCOPE_IDENTITY().

Generating primary keys

Oracle
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;
SQL Server
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();

📋 Key differences

  • 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. IDENTITY is a property of one specific column on one specific table; it can't be shared.
  • NEXTVAL both advances and returns the sequence's counter; you call it inline inside the INSERT. 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.
  • CURRVAL reads the last value your session generated from a specific sequence. SCOPE_IDENTITY() reads the last identity value generated by an insert in the current scope (same stored procedure/batch) — closely analogous, but scoped to execution context rather than to a named sequence object.
  • SQL Server also has @@IDENTITY (last identity value generated in the current session, across scopes) and IDENT_CURRENT('table') (last identity value for a specific table, across sessions) — prefer SCOPE_IDENTITY() unless you specifically need one of those wider scopes.

⚠️ Gotcha

@@IDENTITY looks like the obvious choice because it's shorter and older, but it returns the last identity value generated anywhere in the session — including inside a trigger fired by your insert. If a trigger on the employees table inserts into an audit table that has its own IDENTITY column, @@IDENTITY silently returns the audit table's new ID, not the employee's. SCOPE_IDENTITY() avoids this trap by staying within the same scope as your original insert — use it by default.

The modern bridge: Oracle 12c+ IDENTITY columns

Oracle 12c+
CREATE TABLE employees (
  employee_id NUMBER GENERATED ALWAYS
              AS IDENTITY,
  first_name  VARCHAR2(50)
);

INSERT INTO employees (first_name)
VALUES ('Ada');
SQL Server
CREATE TABLE employees (
  employee_id INT IDENTITY(1,1) PRIMARY KEY,
  first_name  VARCHAR(50)
);

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

If your Oracle codebase is on 12c or later, it likely already uses GENERATED ALWAYS AS IDENTITY — 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. If you recognize this pattern from your Oracle code, you can think of SQL Server's IDENTITY as "that, but it's been the default approach since SQL Server's earliest versions" rather than as something new.

🔧 Try It Yourself

Take an Oracle table that uses a classic SEQUENCE plus a BEFORE INSERT trigger to populate its primary key (a very common pre-12c pattern) and rewrite it as a single SQL Server CREATE TABLE with an IDENTITY(1,1) column — notice you can delete the trigger entirely, since the column definition does that job on its own.

🙋 There Are No Dumb Questions

Q: Can I still use a sequence-like object in SQL Server if I need one shared across multiple tables?
A: Yes — SQL Server has had its own standalone CREATE SEQUENCE object since SQL Server 2012, with NEXT VALUE FOR sequence_name playing the same role as Oracle's NEXTVAL. It's less commonly reached for than IDENTITY because most single-table auto-numbering needs are simpler with IDENTITY, but it exists for the cases — like sharing one counter across several tables — where IDENTITY can't help.

✅ Quick Check

You insert a row into a SQL Server table that has an IDENTITY primary key, and a trigger on that table also inserts into a separate audit table with its own IDENTITY column. Which function reliably returns the ID generated by your original insert, not the trigger's?

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

← Back to
Next →