Sequence is a new feature introduced in SQL Server 2012. Unlike identity values, sequence is an independent object. It can be used in table definition as a default constraint on a column or referenced from a query including OVER clause. There are few DDLs and DMLs supporting sequences.
- Create Sequence: create a sequence optionally with properties, returning type, starting value, interval, minimum value, maximum value, cycle, cache. If you don’t specify any properties, bigint will be used as returning data type.
- Alter Sequence: Modify properties of sequence, returning type, starting value, interval, minimum value, maximum value, cycle, cache
- Drop Sequence: Remove a sequence.
- Next Value For: Get next value of a sequence.It cannot be called from functions.
- sp_sequence_get_range: Get range of sequence.
- sys.sequences: DMV for sequence.
Sequence Structre
When you create an sequence, a record will be added to sys.sysschobjs which host the name of the the sequence with properties such as creation date, last modify date etc. Another record will be added to sys.sysobjvalues which is to save the properties of a sequence. Please see the code snippet below and run is under admin mode
1 |
if object_id('mySequence') is not null |
2 |
drop sequence mySequence |
3 |
create sequence mySequence; |
5 |
select @object_id = object_id('mySequence') |
6 |
print format(@object_id, 'object_id = 0') |
7 |
select * from sys.sysschobjs where id = @object_id |
8 |
select valclass,objid,subobjid,valnum,value from sys.sysobjvalues where objid = @object_id |
What will be returned are
02 |
id name nsid nsclass status type pid pclass intprop created modified status2 |
03 |
----------- ------------ ----------- ------- ----------- ---- ----------- ------ ----------- ----------------------- ----------------------- ----------- |
04 |
581577110 mySequence 1 0 512 SO 0 1 0 2011-11-01 01:17:36.507 2011-11-01 01:17:36.507 0 |
08 |
valclass objid subobjid valnum value |
09 |
-------- ----------- ----------- ----------- -------------------------------------------------------------------------------------- |
10 |
63 581577110 0 4991 0x000000000000008001000000000000000000000000000080FFFFFFFFFFFFFF7F000000000000008001 |
The interpretations of the returning values are
- Line 1: it’s an independent object. You can use meta data function, object_id(), object_name(), object_schema_name(), etc, to get names or ids.
- Line 4: Object type is SO. Column intprop is the cache size. In this case the cache size is 0 which means use default size 50. Column status records whether or not the sequence value is cached.
- Line 10: rest of the definition of the sequence, min value, max value, current value
How Sequence Works
No Cache defined: For each of NEXT VALUE FOR calls, SQL Server will apply a SCH_S lock on object mySequence to ensure there is no schema modification allowed while getting next value, then apply IX lock on sys.sysobjvalues table and U lock on the definition (line 10 above), modified the current value in value field, and finally release all locks. If there is no cache, every NEXT VALUE FOR call will perform an update to an system table. This update is not regular update. It uses update lock to lock the record instead of exclusive locks. This can increase the concurrency of system tables and also reduce the overhead of lock conversion (converting U lock to X lock, for more detail, see my locking and blocking series)
Cache are defined: When a cache is defined, SQL Server internally does NOT maintain a list of values, instead, it only keeps 2 values, one is the first value and another one is the last value. When NEXT VALUE FOR is called, the sequence number returned from cache. No further update to system table needed at this moment. When the cache is exhaused, the next value and the last value for the sequence are re-calculated, and then the last value will be saved in the sys.sysobjvalues table in the same way as explain in previous paragraph. When the server is shutdown, the current value of the sequence will be re-written to the this table but if the server is terminated, you will lose values from current value to the last value in the cache.
Default Cache: When neither cache nor no cache are specified, default cache is used. The size of it is 50.
sys.sp_sequence_get_range: This is the procedure get range of values. It calls an internal system procedure, sys.sp_sequence_get_range_internal. This procedure is very efficient. If cache is defined, range will be read from cache. If no cache is defined, performance of this procedure will be as quick executing NEXT VALUE FOR once.
Performance of Squence
03 |
if object_id('a') is not null |
05 |
if object_id('seq1') is not null |
08 |
create table a (id bigint identity(1,1)) |
10 |
declare @i int, @d datetime2(3), @j int |
11 |
select @i = 0, @d = GETDATE() |
15 |
insert into a default values |
19 |
select 'Identity', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
21 |
create sequence seq1 no cache |
23 |
declare @i int, @d datetime2(3), @j bigint |
24 |
select @i = 0, @d = GETDATE() |
27 |
select @j = next value for seq1 |
30 |
select 'Sequence Without Cache', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
34 |
create sequence seq1 cache |
36 |
declare @i int, @d datetime2(3), @j bigint |
37 |
select @i = 0, @d = GETDATE() |
40 |
select @j = next value for seq1 |
43 |
select 'Sequence With Cache 50', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
47 |
create sequence seq1 cache 500 |
49 |
declare @i int, @d datetime2(3), @j bigint |
50 |
select @i = 0, @d = GETDATE() |
53 |
select @j = next value for seq1 |
56 |
select 'Sequence With Cache 500', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
60 |
create sequence seq1 cache 5000 |
62 |
declare @i int, @d datetime2(3), @j bigint |
63 |
select @i = 0, @d = GETDATE() |
66 |
select @j = next value for seq1 |
69 |
select 'Sequence With Cache 5000', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
71 |
declare @i int, @d datetime2(3), @j bigint |
72 |
select @i = 0, @d = GETDATE() |
77 |
select 'Just Loop', DATEDIFF(millisecond, @d, getdate()) Milliseconds |
The result for generating 2 million values:
So the conclusion is that identity is 10 times slower than sequence with default cache size. You will not gain significant performance by defining very big cache size.
