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

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}");
	}

}

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'

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

SQL query results showing the returned ID

Leave a Comment