SQL Server Sequence Number

SQL Server Sequence Number

Rate This
  • Comments 12

Simulating Sequence Objects in SQL Server

 

Many applications need sequentially incremental number as unique/primary key of records.  SQL Server 2005 today supports identity column as the primary mean to general sequence number, which generates the sequence number upon the execution of DML (insert) or bulk insert.  The value of the insertion is known (when using insertion DML), post the execution of the DML.  In some implementations, the sequence numbers (or next unique incremental number) need to be generated prior to the DML operation.  For example, if sequence number can be retrieved prior to the DML operation, it can be easily implemented in situations where multiple DML (inserts, or even updates) using the same sequence number can be performed, and data can be insert into different tables, for example, parent-child, or based on the sequence value, data can be insert into different tables.

 

SQL Server 2005 Migration Assistant (SSMA) provides a different mechanism for sequence number generation.  SSMA’s approach is to use a single table to hold all the sequence numbers.  Each sequence number object is represented by a single row of record, which holds the property of the sequence, e.g. sequence name, current value, increments…etc.  The generation of the next value is invoked by an extended stored procedure in order to minimize the impact on application concurrency – the execution of extended procedure is not within the transaction scope of the DML statement, and it holds very short duration locks on the row that represents the sequence number.

 

This BLOG describes two more methods of sequence number generation. 

 

Option 1

This option is easy to use and maintain, but it is suitable only with lower volume invocations.  It is also one of the widely used approaches, and often misused.  This method uses a single table to hold all the sequences needed for the application, and each sequence is represented by one single record, thus serializes operation requesting next value of the same sequence number. 

 

 

1.      Create the following objects

 

Table AllSequences holds the information of all the sequences.  As a simple example, the table and subsequent stored procedure is kept quite simple.  Further enhancement is certainly possible based on the needs – for example, additional information of sequence such as maximum range, minimum range, loopback(yes/no) can be added.

 

·         SeqName – name of the sequence

·         Seed – seed value of the sequence

·         Incr – incremental value of the sequence

·         Currval – current value of the sequence

 

 

Create table AllSequences (

      SeqName nvarchar(255) primary key, -- name of the sequence

      Seed int not null default(1), -- seed value

      Incr int not null default(1), -- incremental

      Currval int

)

Go

 

The following stored procedure is used to create a sequence.  The following input parameter is expected:

·         SeqName – name of the sequence

·         Seed – seed value of the sequence

·         Incr – incremental value of the sequence

 

 

create procedure usp_CreateNewSeq

      @SeqName nvarchar(255),

      @seed int = 0,

      @incr int = 1

as

begin

      declare @currval int

      if exists (

            select 1 from AllSequences

            where SeqName = @SeqName )

      begin

            print 'Sequence already exists.'

            return 1   

      end

 

      if @seed is null set @seed = 1

      if @incr is null set @incr = 1

      set @currval = @seed

 

      insert into AllSequences (SeqName, Seed, Incr, CurrVal)

      values (@SeqName, @Seed, @Incr, @CurrVal)

     

end

go

 

 

The following stored procedure is used to generate the next value of the specified sequence through input parameter SeqName.

 

create procedure usp_GetNewSeqVal

      @SeqName nvarchar(255)

as

begin

      declare @NewSeqVal int

      set NOCOUNT ON

      update AllSequences

      set @NewSeqVal = CurrVal = CurrVal+Incr

      where SeqName = @SeqName

     

      if @@rowcount = 0 begin

print 'Sequence does not exist'

            return

      end

 

      return @NewSeqVal

end

go

 

    

2.      To create the new sequence, for example:

usp_CreateNewSeq N'TestSeq'

 

3.      To retrieving the new sequence value, for example for sequence “TestSeq”

usp_GetNewSeqVal N'TestSeq'

 

One thing to be careful is to keep the execution of the stored procedure usp_CreateNewSeq outside the scope of subsequent transaction which uses the sequence number generated.  This can minimize impact of blocking.

 

Typical usage

Declare @NewSeqVal int

Execute @NewSeqVal=usp_GetNewSeqVal @seqname=N'TestSeq'

print @NewSeqVal

 

Option 2

This method is intended for higher-throughput environments – such as thousands of transactions per second.  A table with identity column is created for every sequence.  Since the generation of identity value is lightweight, and not within the transaction scope of subsequent DML, this method can significantly improve the concurrency over the first method.  In addition, it also consumes less resource than extended procedure. 

 

 

1.      Create one table with the following structure for each sequence, assuming seed 1, with increment of 1).

create table <tablename> (

      SeqID int identity(1,1) primary key,

      SeqVal varchar(1)

)

 

2.      Create the following stored procedure.  A delete operation is performed to remove the rows in the sequence table to reduce maintenance.  This obviously adds some load to the transaction however it should be quite minimal.  As an alternative, for higher throughput, the delete statement can be removed and a maintenance job can be scheduled during off-hour to purge the records in the table (truncate should not be used since it resets the identity value).

create procedure GetNewSeqVal_<tablename>

as

begin

      declare @NewSeqValue int

      set NOCOUNT ON

      insert into <tablename> (SeqVal) values ('a')

     

      set @NewSeqValue = scope_identity()

     

      delete from <tablename> WITH (READPAST)

return @NewSeqValue

end

 

3.      To retrieve the new value in a result set, simply execute the following:

GetNewSeqVal_<tablename>

 

 

Typical usage

            Declare @NewSeqVal int

            Exec @NewSeqVal =  GetNewSeqVal_<tablename>

           

 

Among the two options described above, Option 1 is widely used in different variations but prone to blocking/deadlocking.  Option 2 improves concurrency significantly with a simple approach.

 

 

Howard Yin

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • Very useful! But ... why can't SQL Server 2005 just implement sequences natively ? That would make life so much simpler, and avoid the need for workarounds such as this. Now if you could get it into 2005 SP1, that would be great :-).
  • I'm a bit confused about Option 2.  Why did you add the second column?  Why not just have a single column and do:

    INSERT <tablename>
    DEFAULT VALUES

    ?

    Does the additional column help with something in this scenario?

  • The method you described certainly will work just fine.  The key of this post is to perform the generation of sequence number prior to DML  inserts through an identity column based dummy table to improve concurrency.

    There can be many variation, and enhancements in different implementations.  The sample code is just for illustrative purpose :)

    One option can be using the extra field (of course a larger size) to hold additional information of the sequence, such as min/max, and loopback flag - through a dummy record.  And the stored procedure "GetNewSeqVal..." can be enhanced to perform some checks.
  • SQL Server 2005 includes a lot of changes to help make moving from Oracle to SQL Server easier, these

  • You can avoid accumulating data in the sequence table by putting the INSERT statement inside a nested transaction with a save point that you then rollback.

    begin tran

    save tran seq

    insert into <tablename> default values

    set @NewSeqValue = scope_identity()

    rollback tran seq

    commit

  • Now, It is in SQL Server Denali...:)

    Sequence Object

  • very usefull, it will be better and simpler if sql server uses sequences types.

  • how to implement, managing sequences and performance which is better identity or sequences please use the below link

    sqljunkieshare.com/.../sequences-in-sql-server-2012-implementingmanaging-performance

  • I am looking for a BigInt datatype in SQL Server for a high volume application.

    Can you help?

  • Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012. A Sequence object is an object that provides functionality similar to Identity (Autonumber) column

    The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value before you insert records.

    Kash Mughal

    Senior DBA and Trainer

    www.sqlserver2012tutorial.com

  • how would you cretae a function that returns sequential number?

Page 1 of 1 (12 items)