SQL Server 2012 Sequences

Microsoft introduced sequences in SQL Server 2012. These have existed for some time in ORACLE. SQL Servers sequences have some similarities to identity columns. They present a number of opportunities for the developer and DBA. In particular, Microsoft claim performance and manageability improvements. I will examine several aspects of sequences, including performance, re-cycling and the use of the procedure sp_sequence_get_range to reserve ranges of numbers.

Catalog View

The catalog view sys.sequences holds a row for each sequence. Some useful queries are:

To retrieve the current value for a specific sequence:

select current_valueFROMsys.sequences
WHEREname='seqJournalID';

To identify any sequences which are approaching the maximum value:

select name,current_value,maximum_value,
caseis_cycling
when 1 then'Cycle ON'
else'Cycle OFF'end
FROMsys.sequences

Performance

The sequence should generate considerably faster performance than (for example) an identity column. This is
 because the sequence is maintained in memory. To explore this, I ran a simple test using the following TSQL:

create tableTident(idbigintidentity(1,1))
go
createtableTseq(idbigint)
go
CREATESEQUENCE[dbo].[seqTest]
CACHE 500
go
declare @sdatedatetime2(3)
declare @i int= 0select @sdate=GETDATE()while @i< 250000
begin
  insertintoTidentdefaultvalues
  set@i=@i+ 1
end

select 'Test Identity runtime',DATEDIFF(second,@sdate,getdate())seconds
set @i= 0
select@sdate=GETDATE()
while @i< 250000
begin
insertintoTseq(id)values (NEXTVALUEFORseqTest)
set@i=@i+ 1
end
select 'Test sequence runtime',DATEDIFF(second,@sdate,getdate())seconds

I ran this test on a laptop. The results are near identical – 66 seconds for each while loop. This may not be a  valid test – I have made little attempt to tune the sequence cache. In a multi-client situation I would expect  that the cache could generate performance improvements.

–
Maximum values and cycling

Sequences can be generated with explicit maximum values or with implicit maximum values (defined when the data type is selected). If the sequence has reached its maximum value, it may error or re-cycle the numbers again.
The following TSQL illustrates this.

First, create a sequence as tinyint – this means that the maximum value will be 255.

CREATE SEQUENCE[dbo].[JournalSeq] 
AS[tinyint]
START WITH 1
INCREMENT BY 1

Run the following TSQL to test this.

Insert into T1(c1,c2)
values (NEXTVALUEFORJournalSeq,'test sequence');
go 300

After inserting a number of rows, the TSQL fails with the following error:

Msg 11728, Level 16, State 1, Line 2
 The sequence object 'JournalSeq' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated

The sequence can be re-set using the following TSQL:

 alter SEQUENCE JournalSeq RESTART

As an alternative, consider using the CYCLE clause of the Create Sequence as below:

CREATE SEQUENCE[dbo].[JournalSeq2]
AS[tinyint] 
START WITH 1 
INCREMENT BY 1
CYCLE

Now run the following TSQL to test:

Insert intoT1(c1,c2)
values (NEXT VALUE FOR JournalSeq2,'test sequence');
go 300

This time, the sequence automatically re-starts – from 255 to 1.

Amending Sequences

Unlike identity columns, after generating a sequence number, the column can be updated. For example, the below code works:

CREATE SEQUENCE[dbo].[JournalSeq2] 
AS [tinyint]
START WITH 1
INCREMENT BY 1
CYCLE
GO
Insert intoT1(c1,c2)
values (NEXT VALUE FOR JournalSeq2,'test sequence');
go 300 
update T1 set c1=0;

If the sequence column is defined as a varchar, it becomes possible to incorporate the sequence into string functions to generate text strings. For example,

insert
into T1(c1,c2)
values ('Fr'+cast(NEXTVALUEFORJournalSeq2 as varchar(5)),'French company');

updateT1setC1='UK'+C1 where c2='British Company';

I am not convinced that being able to update sequences is a good thing – there is a danger that if the sequence is used as a key then data relationships could be lost. Creating constraints on the sequence column should remove some of the risk, but this is not something to employ without care.

Sequence Ranges

Possibly the most interesting aspect of the sequence is the way that a range of sequences can be used. The following code demonstrates this by creating a sequence, and then using sequence_get_range to ‘reserve’ 5 slots. After running this procedure, the sequence has incremented by 5 (I check this by accessing the sys.sequences DMV).

CREATE SEQUENCEseqRange
AS int
START WITH 1;

declare @first_valuesql_variant,
       @first_value_outputsql_variant;

exec sp_sequence_get_range 
@sequence_name =N'seqRange',@range_size= 5,@range_first_value=@first_value_output output;
select @first_value_outputasFirstNumber;
select current_valuefromsys.sequences wherename='seqRange';

I think that this feature will be used in applications where it is necessary to ‘reserve’ several values to work with – for example inserting data where the application wants to ensure that the rows are located in close proximity for performance reasons.