SQL Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

There are multiple ways to increment ids in SQL Server:

  • IDENTITY columns
  • SEQUENCEs (added in SQL Server 2012)
  • Atomic UPDATEs with the OUTPUT clause

In this article I’ll show the third way – using an atomic UPDATE with OUTPUT.

Tables – Ids and Users

I have the following two tables – Ids and Users:

Ids table - Type and Id column
Ids table
Users table - Id, Name, and Job column
Users table

Stored Proc for creating users with unique ID

When I insert a new user I want to:

  • Increment the user ID.
  • Create the user record with the next user ID from the Ids table.
  • Return the user ID (so the calling application gets it).
CREATE PROCEDURE [dbo].[spCreateUser] @Name nvarchar(50), @Job nvarchar(50) AS BEGIN UPDATE Ids SET Id = Id + 1 OUTPUT INSERTED.Id, @Name, @Job INTO Users OUTPUT INSERTED.Id WHERE [Type] = 'User' END
Code language: SQL (Structured Query Language) (sql)

UPDATE with OUTPUT is an atomic operation. If this query is executed concurrently, each execution will generate a unique ID.

Execute the stored proc

Using C#

Here’s how to execute the stored proc and get the returned ID using C#. It uses the ExecuteScalar() statement because the query is returning a single result.

static async Task Main(string[] args) { var conString = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString; using (SqlConnection con = new SqlConnection(conString)) { var id = await con.ExecuteScalarAsync("[spCreateUser]", new { name = "Alice", job = "Dev" }, commandType: CommandType.StoredProcedure); Console.WriteLine($"Id = {id}"); } }
Code language: C# (cs)

Note: This is using Dapper.

When I run this it writes “Id = 2” to the console.

Using SSMS

To execute this query directly in SSMS, I can use the following:

EXECUTE [dbo].[spCreateUser] 'Alice' ,'Dev'
Code language: SQL (Structured Query Language) (sql)

When I execute this it shows the returned Id in the Results tab.

SQL query results showing the returned ID

Leave a Comment