How to implement generators in MSSQL server

Why Server‑Side Key Generation Is Essential

When a client application builds records before sending them to a relational database, it often needs a unique identifier for each new row. The identifier may be a primary key or a foreign key that links to another table. In distributed or slow‑network scenarios, the client usually caches data locally, manipulates it, and then pushes it back to the server. If the server cannot supply the key values ahead of time, the client must wait for an insert to finish, read back the identity, and then continue processing. That pause breaks the flow and can cause deadlocks or lost updates when multiple clients compete for the same table.

Relational database systems offer several built‑in mechanisms for generating unique values. SQL Server provides identity columns, sequences, and the newer IDENTITY‑WITH‑SEED or IDENTITY‑WITH‑INCREMENT features. All of these allow the database to produce a value automatically when a row is inserted. However, some legacy applications or design constraints still rely on a separate “generator” table that isolates the identity logic from the main business tables. Using a dedicated generator has the advantage of centralizing key creation, making it easier to audit or change the strategy without touching every table definition.

Consider a real‑world example: a corporate intranet site that lets users create project plans. Each plan requires a unique numeric ID that also becomes part of a reporting code visible to end users. The site runs on an older server with a bandwidth‑limited connection to the database. Users might create dozens of plans offline, then upload them in bulk. To preserve referential integrity, each plan must carry a valid ID before it can be linked to its tasks. Waiting for the server to respond after each insert would degrade performance and frustrate users. A pre‑generated key from a shared generator table resolves this issue.

Even when the application is not cache‑heavy, the same principle applies. If the design calls for inserting a row into a foreign‑key dependent table, the client must already know the foreign key value to maintain consistency. The generator table solves this by providing a reliable, server‑side source of values that any client can request, ensuring the database’s integrity constraints are respected.

Beyond performance, key generators reduce the risk of accidental duplicate values. An identity column guarantees uniqueness as long as the table is properly defined. But if you need to generate keys for multiple tables from a single source, you must enforce uniqueness yourself. A generator table that holds a single identity column is a simple, proven pattern that eliminates this risk. Moreover, the generator can be locked or version‑controlled, giving you tighter control over how new keys are issued.

In addition to the practical advantages, the generator pattern aligns with modern best practices for distributed systems. By delegating key generation to the database, you avoid complex client‑side logic, reduce network traffic, and keep the application stateless. The database guarantees atomicity; the transaction that inserts into the generator table is a single, uninterruptible operation. This atomicity protects against race conditions that could otherwise lead to duplicate keys or lost updates.

Understanding these benefits lays the groundwork for building a generator that is both simple and robust. The next step is to design the generator table and the stored procedure that will supply new keys on demand. In the following section we’ll walk through the SQL needed to create these objects, and we’ll examine the key differences between using @@IDENTITY, SCOPE_IDENTITY, and the OUTPUT clause.

Creating the Generator Table and a Basic Stored Procedure

Start by creating a lightweight table that will serve as the key pool. The table only needs one identity column and, optionally, a dummy column to satisfy the requirement that the INSERT statement lists at least one column. The identity column automatically increments each time a row is added, guaranteeing a unique value.

sql

CREATE TABLE dbo.GeneratorTable

(

Id INT IDENTITY(1,1) NOT NULL,

Dummy CHAR(1) NULL

);

When you insert into GeneratorTable, you supply a value for Dummy but leave Id untouched. The database then assigns the next available integer to Id. The row persists in the table unless you delete it later, but in many generator designs you don’t bother removing it. The row count grows, but that is inconsequential because the table’s sole purpose is to generate numbers, not to hold data.

Next, write a stored procedure that will perform the insert and return the new identity value. The classic way uses @@IDENTITY, which reflects the last identity value generated by the current session, regardless of scope. A more reliable choice is SCOPE_IDENTITY(), which limits the result to the current procedure or batch. This eliminates the risk that a trigger on another table will generate its own identity value and cause the procedure to return the wrong number.

sql

CREATE PROCEDURE dbo.GetNextKey

AS

BEGIN

INSERT INTO dbo.GeneratorTable (Dummy) VALUES (NULL);

SELECT SCOPE_IDENTITY() AS NextKey;

END;

In many applications, you may prefer to return the value directly from the procedure using the RETURN statement. However, using SELECT is more flexible because the client can capture the result set without having to parse the return code. The example above returns a single row with a column named NextKey. The client can use EXEC dbo.GetNextKey and read the value from the result set.

It is essential to test the procedure from a SQL client to confirm it behaves as expected. For instance, using SQL Server Management Studio’s query editor, you can execute:

sql

DECLARE @Key INT;

EXEC @Key = dbo.GetNextKey;

PRINT @Key;

The PRINT statement will display the generated key. Repeating the command should produce incrementally larger numbers.

When the generator is called concurrently from multiple sessions, SQL Server handles the inserts atomically. The identity column increments safely even under heavy load, and SCOPE_IDENTITY() guarantees that each session receives the correct value. If you use @@IDENTITY instead, a trigger that inserts into another table with an identity column could corrupt the result. That is why SCOPE_IDENTITY() is the preferred method for key generators.

Another common requirement is to support group inserts, where a single INSERT statement adds multiple rows. In such cases, you can use the OUTPUT clause to capture all generated identities in one operation. The generator approach can be adapted by having the business table’s identity column serve the same purpose, but if you stick with a dedicated generator, you must call the procedure once per row, which can be slower. To mitigate this, you could write a batch procedure that loops over an input table variable, inserts into GeneratorTable for each row, and collects the results into a temporary table. The client can then read that table after the batch completes.

Overall, the table-and-procedure pattern is straightforward, portable across SQL Server versions, and easy to maintain. By isolating the key generation logic, you preserve clean separation of concerns and keep the business tables free from extra columns or complex constraints.

Optimizing for Concurrency and Cleanliness

Although the basic generator works well under normal load, real‑world deployments sometimes reveal subtle concurrency problems. When two or more sessions attempt to call the generator simultaneously, SQL Server places a lock on the inserted row until the transaction commits. If the generator table is small and the transaction is brief, the contention is minimal. However, if the generator is used heavily, the locks can serialize access and become a bottleneck.

One way to reduce contention is to use a locking hint that tells SQL Server to acquire an exclusive lock on the row but to skip any rows that are already locked by other transactions. The ROWLOCK hint forces row‑level locking, while the UPDLOCK hint ensures the lock is held until the transaction ends. In a generator, you typically do not need to lock multiple rows, so adding ROWLOCK is sufficient. A more aggressive strategy is to use READPAST to skip locked rows, but that is less useful for a generator because you want every call to succeed.

Another optimization is to eliminate the inserted row after you read its identity value. In the basic generator, the row remains in GeneratorTable, and over time the table can grow large. While the growth is not harmful, it can clutter the database. A common pattern is to insert the row, capture the identity, and then delete the row in the same transaction. Because the delete is performed in the same transaction, the row never actually persists. This keeps the table empty and ensures that each insert remains a single operation.

Below is an improved procedure that encapsulates this logic:

sql

CREATE PROCEDURE dbo.GetNextKeyOptimized

AS

BEGIN

SET NOCOUNT ON;

DECLARE @NewId INT;

BEGIN TRAN;

INSERT INTO dbo.GeneratorTable (Dummy) VALUES (NULL);

SET @NewId = SCOPE_IDENTITY();

-- Remove the temporary row

DELETE FROM dbo.GeneratorTable

WHERE Id = @NewId;

COMMIT TRAN;

SELECT @NewId AS NextKey;

END;

This procedure follows a simple pattern: start a transaction, insert a row, read the identity, delete the row, and commit. The delete is safe because the row is unique; no other session can reference it. The transaction ensures that the sequence of operations is atomic. If the transaction fails, none of the steps persist, and the next call will retry.

When dealing with high concurrency, you may also want to consider using a sequence object introduced in SQL Server 2012. Sequences provide a separate, database‑wide value generator that is independent of tables and supports caching. Using a sequence eliminates the need for a generator table altogether:

sql

CREATE SEQUENCE dbo.KeySequence

AS INT

START WITH 1

INCREMENT BY 1

NO CACHE; -- or CACHE 1000 for higher performance

-- Retrieve a new key

SELECT NEXT VALUE FOR dbo.KeySequence AS NextKey;

Sequences handle concurrency internally, so they are often faster than the table‑based approach. However, they are not available in older SQL Server versions, and some legacy applications expect a table‑based generator. The choice depends on your environment and compatibility requirements.

Finally, consider the scenario where you need to generate keys for a batch of inserts. If you call the generator once per row, the overhead can become significant. A more efficient method is to generate a batch of keys in a single transaction and return them as a table-valued result set. The client can then map each key to its corresponding data row before performing the bulk insert. Here’s a skeleton procedure that demonstrates this idea:

sql

CREATE PROCEDURE dbo.GetBatchKeys

@Count INT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @Keys TABLE (KeyValue INT);

DECLARE @i INT = 0;

WHILE @i

BEGIN

INSERT INTO @Keys (KeyValue)

VALUES (SCOPE_IDENTITY());

WHERE Id = SCOPE_IDENTITY();

SET @i = @i + 1;

END

SELECT KeyValue FROM @Keys;

END;

After calling GetBatchKeys with the desired count, the client receives a set of unique identifiers that it can pair with its data rows. This reduces round‑trips and improves throughput on bulk operations.

By applying these concurrency‑aware techniques and optional sequence objects, you can keep the generator fast, clean, and ready for production workloads. The choice of pattern - simple insert‑return, delete‑after‑use, or batch generation - depends on your specific performance targets and architectural constraints.