Lesson 3 of 5

Auto-Increment & Identity

Every table needs a way to generate its own primary key values, and both engines automate it — but the keyword, the function you call afterward to retrieve the value you just inserted, and the scoping rules around that function are all different enough to trip up a direct port.

💡 The Bottom Line

AUTO_INCREMENT becomes IDENTITY(1,1), and LAST_INSERT_ID() becomes SCOPE_IDENTITY(). The biggest behavioral gap isn't the keyword — it's that LAST_INSERT_ID() is scoped per-connection (it returns the last auto-increment value generated by any statement on that connection, including one fired by a trigger), while SCOPE_IDENTITY() is scoped per-batch/ per-caller: it deliberately won't pick up an ID generated by a trigger running in a different scope, which is exactly the case where MySQL's function can surprise you.

Declaring an auto-incrementing primary key

MySQL (what you know)

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
        CHARACTER SET utf8mb4 NOT NULL
);

SQL Server (the new equivalent)

CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL
);

📋 Key differences

MySQL's AUTO_INCREMENT takes no arguments in the column definition — you set the starting value afterward with ALTER TABLE orders AUTO_INCREMENT = 1000;, and the increment is a server-wide setting, not a per-column one. SQL Server's IDENTITY(seed, increment) lets you set both the starting value and the step size inline, per column, right in the CREATE TABLE statement.
MySQL requires the AUTO_INCREMENT column to be indexed — almost always as the primary key or part of one. SQL Server allows exactly one IDENTITY column per table, and it doesn't strictly have to be the primary key (though it usually is).
Both engines reject explicit inserts into the auto-generated column unless you override the behavior — MySQL just lets you insert an explicit value directly (including 0 or NULL to still trigger auto-generation), no special mode required; SQL Server needs an explicit SET IDENTITY_INSERT Orders ON statement first, which you must remember to turn back off afterward.

Retrieving the value you just inserted

MySQL (what you know)

INSERT INTO orders (customer_name)
VALUES ('Ada Lovelace');

SELECT LAST_INSERT_ID();

SQL Server (the new equivalent)

INSERT INTO Orders (CustomerName)
VALUES ('Ada Lovelace');

SELECT SCOPE_IDENTITY();

⚠️ Gotcha

Don't reach for SQL Server's other identity functions out of habit, expecting them to behave like LAST_INSERT_ID(). @@IDENTITY returns the last identity value generated in the current session, across any scope — including a value generated by a trigger — which is exactly the connection-wide behavior MySQL developers are used to, and exactly the bug SCOPE_IDENTITY() exists to avoid. If an AFTER INSERT trigger on the same session inserts into another identity-having table, @@IDENTITY can silently return that table's value instead of the one you expected — SCOPE_IDENTITY(), scoped to only the current batch/stored procedure, is immune to that and is almost always the right choice.

Composite and non-integer keys

MySQL and SQL Server both support composite primary keys (multiple columns together) and both let a primary key be a non-integer type like CHAR(36)/UNIQUEIDENTIFIER for GUID-based keys — but only one column in a SQL Server table may ever carry IDENTITY, and it doesn't have to be part of a key at all (unlike MySQL, which requires AUTO_INCREMENT to be indexed). A common pattern from MySQL where an AUTO_INCREMENT column is just a convenience surrogate alongside a separately-enforced natural key works the same way in SQL Server: keep the IDENTITY INT as the primary key, and add a separate UNIQUE constraint or index for the natural key (e.g. an order number or email).

🔧 Try It Yourself

Write a SQL Server INSERT into a table with an IDENTITY primary key, followed immediately by SELECT SCOPE_IDENTITY(); in the same batch. Then, in a second session (a second query window), insert a row into the same table and call SCOPE_IDENTITY() there too — confirm each session only ever sees its own most recent value. Then try SELECT @@IDENTITY; instead and think through a scenario (an AFTER INSERT trigger on the table) where it could return a different value than SCOPE_IDENTITY() would.

🙋 There Are No Dumb Questions

Q: If two people insert into the same SQL Server table at the same time, can SCOPE_IDENTITY() return the wrong row's ID?
A: No — that's exactly the problem SCOPE_IDENTITY() is designed to avoid. It's scoped to the current session and the current execution scope (batch, stored procedure, or trigger), not to the table, so concurrent sessions each get their own private view of "the last value I generated," even though the underlying IDENTITY counter is shared and strictly increasing across the whole table. This is actually a slightly narrower, safer guarantee than MySQL's LAST_INSERT_ID(), which is scoped only per-connection, not per-scope — a trigger firing within the same connection can still shadow the value in MySQL, whereas SCOPE_IDENTITY() would not be affected by that trigger's own insert.

✅ Quick Check

Why might SCOPE_IDENTITY() return a different value than @@IDENTITY right after an INSERT that fires an AFTER INSERT trigger which itself inserts into another identity-having table?

Next up: schemas and keys are in place — time to look at the procedural layer, where CREATE PROCEDURE syntax and variable declarations diverge more than almost anything else between the two engines.

← Back to