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:

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.