How to implement generators in MSSQL server

	How to implement generators in MSSQL server

Submitted on June 25, 2002

One of the important properties of any client-server application is its serviceability and an intuitive user interface. Many widely used relational database management systems have a lot of opportunities to choose different user interface features. Intending to make a program decision easy and convenient, and, at the same time, to keep the referential integrity of the database, it is often necessary to know the values of the key fields of the processed tables before their real inserting. For example, imagine, we have a slow connection to our database that is why we have some records cache on the client side and we have to process several linked records simultaneously. To do that, we should get all primary keys first and then setup the corresponding foreign keys.

This and the same problems are solved successfully by using the unique value generators on the server side. Such generators are implemented in the Interbase Server, but others may not have them. Nevertheless there is a possibility to solve this problem using identity columns. In this article, we are discussing one of different ways to implement the key generator for Microsoft SQL server.

Some of the main requirements, which the generators should satisfy and which the auto incremented columns do, are an originality of the generated values and work out server transactions. The last requirement can be made more flexible if the so-called short transaction between the server and the client are implemented. It means there are no long-time transactions with modified data.

The basic part of this generator is a table which has an identity column.

CREATE TABLE [dbo].[GENERATOR_TABLE] (
[ID] [int] IDENTITY (1, 1) NOT NULL,
[DUMMY] [char] (1) NULL
)

All working algorithm of the generator is to insert a record into that table and, next, to read a unique value from the column [ID]. The meaning of the second column is obviously seen from the INSERT statement syntax (accordingly to the SQL-92 standard), that is we must show at least one column in the statement body, but, at same moment, we cannot insert a value into a auto incremented column (see MSDN articles).

CREATE PROCEDURE [dbo].[GENERATOR_PROC] AS
BEGIN
INSERT INTO GENERATOR_TABLE ([DUMMY]) VALUES (NULL)
RETURN (@@IDENTITY)
END

In this example we have used the MSSQL server's variable @@IDENTITY, which contains the last identity value generated by the insert statement. It is impossible to use the SELECT statement inside the stored procedure because some locks will appear there if another application calls this generator at the same moment. To test our generator we can call it from SQL Query Analyzer:

DECLARE @ID INT
EXEC @ID = GENERATOR_PROC
PRINT @ID

Nevertheless, this implementation is still far from the perfection and has some defects. Since the inserted records are not used at all, it is really unnecessary to keep them on the server. To avoid the possible mutual locks of the generator when calling from the different connections we have to perform an exclusive row lock for the generator's table. Below there is a better version of this generator.

CREATE PROCEDURE GENERATOR_PROC AS
BEGIN
   BEGIN TRAN
   SAVE TRAN GENERATOR_TRAN
   INSERT INTO GENERATOR_TABLE WITH (ROWLOCK) ([DUMMY]) VALUES (NULL)
   ROLLBACK TRAN GENERATOR_TRAN
   COMMIT TRAN
   RETURN (@@IDENTITY)
END

A call of such a key values generator is possible both from the client application and the server stored procedures and triggers. This generator can be used both one for all tables and one for each table.

In conclusion, we want to note one restriction in comparison with the ordinary system generators. In this version of MSSQL it is really impossible to use that generator in group insert statements. Implementation of such a statement can be made by declaring cursor and sequential moving through all records in the cursor and calling the generator in each loop.

Clever Components Team.
Please feel free to Contact Us
We are welcome to any comments and suggestions.