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
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.
