| Resources for SQL Professionals
Trials, Software, Downloads, and More. |
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.
Reprinted with permission from CleverComponents.com
About the Author:
We welcome any comments or suggestions. Please write to info@clevercomponents.com
|
