Implement Sequence in Sql Server

Problem

In many cases, a database developer would rely on the DBRMS to generate and auto-increment a primary key in a table. In Oracle, for instance, the "Sequence" feature provides an encapsulated functionality to handle incrementing any particular variable. A sequence is not necessarily attached to a particular table, but rather, it's considered an independent entity that can be used amongst several tables.

In Sql Server, however, the sequence feature is replaced by the Identity column feature. One and only one column in any table can be defined as identity, and then the Sql Server will take over the job of incrementing this column with the certain value specified by the developer.

In some cases, a developer might need to make a column auto-incremented while it's not the table's primary key. Since Sql Server allows only one identity in a table, which might sound logical because a table wouldn't and shouldn't have more than one identity, a developer should handle incrementing this column manually.

One solution to this problem can be reading the last value of the field and then incrementing it and inserting the new record with the incremented value. However, this has integrity issue. Suppose two users consume the table simultaneously, both of them will read the same value and increment it with the same amount resulting in the same field for both records.

Solution

Another solution that proved its efficiency is the following:

1. Create a table with the column that you want to be incremented

CREATE TABLE [MyTable](
 
[SomeInfo] [nvarchar](50) NULL,
 
[IncrementedColumn] [int] NULL
 
) ON [PRIMARY]

2. Create a table and call it sequence

CREATE TABLE [Sequence](
 
 [SequenceColumn] [int] NULL
 
) ON [PRIMARY]

3. Insert a value into sequence table to start incrementing from it.

INSERT INTO Sequence (SequenceColumn)
 
VALUES (5)

4. Whenever you want to insert a new record into 'MyTable', update the sequence using the following:

DECLARE @NewSequence INT
 
UPDATE Sequence
SET @NewSequence = SequenceColumn = SequenceColumn + 1

and then you can use @NewSequence to insert a record into 'MyTable'