Get back inserted Id

Getting the Inserted ID in SQL Server (INT vs UNIQUEIDENTIFIER)

When inserting rows into SQL Server, it’s common to immediately need the primary key that was just created—whether to return it from an API, insert related records, or log activity.

The correct approach depends on your primary key type.

This article covers:

• INT IDENTITY primary keys
• UNIQUEIDENTIFIER (GUID) primary keys
• Common mistakes to avoid
• Best-practice SQL patterns
• How this maps cleanly to Dapper / ADO.NET

---

Case 1: INT IDENTITY Primary Keys

Table definition

CREATE TABLE Topics (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100),
    Description NVARCHAR(MAX),
    Image NVARCHAR(255)
);

❌ The common mistake

SELECT @@IDENTITY;

This is unsafe. If triggers or parallel inserts exist, it can return the wrong value.

---

✅ Correct: SCOPE_IDENTITY()

INSERT INTO Topics (Name, Description, Image)
VALUES (@Name, @Description, @Image);

SELECT CAST(SCOPE_IDENTITY() AS INT);

Why this works:

• Scoped to your insert
• Safe from triggers
• Cheap and fast
• Returns only the identity value

---

When to use this

• Simple inserts
• Single-row inserts
• Tables with INT IDENTITY

---

Case 2: UNIQUEIDENTIFIER Primary Keys (GUIDs)

Table definition

CREATE TABLE Topics (
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    Name NVARCHAR(100),
    Description NVARCHAR(MAX),
    Image NVARCHAR(255)
);

❌ Why SCOPE_IDENTITY() does NOT work

SELECT CAST(SCOPE_IDENTITY() AS UNIQUEIDENTIFIER);

SCOPE_IDENTITY() only works with numeric identity columns.

GUIDs are not identities.

---

✅ Correct: OUTPUT INSERTED.Id

INSERT INTO Topics (Name, Description, Image)
OUTPUT INSERTED.Id
VALUES (@Name, @Description, @Image);

This returns the actual GUID generated by SQL Server.

---

Bonus: Multi-row Inserts

SCOPE_IDENTITY() fails here.

INSERT INTO Topics (Name, Description, Image)
OUTPUT INSERTED.Id
SELECT Name, Description, Image
FROM @TopicBatch;

Returns all generated IDs correctly.

---

Dapper Usage

INT

var id = connection.ExecuteScalar<int>(sql, parameters);

GUID

var id = connection.ExecuteScalar<Guid>(sql, parameters);

---

Summary Cheat Sheet

PK Type	Correct Method	Trigger Safe	Multi-Row Safe
INT IDENTITY	SCOPE_IDENTITY()	✅	❌
UNIQUEIDENTIFIER	OUTPUT INSERTED.Id	✅	✅

---

Best Practices

• Use SCOPE_IDENTITY() only for INT IDENTITY
• Use OUTPUT INSERTED.Id for GUIDs
• Never use @@IDENTITY

---

Happy querying 🚀