SEQUENCE in SQLServer

Sequence is a user-defined schema bound object that generates numerical values at regular intervals. It is an auto incremented object introduced in SQL Server 2012.  These numerical values can be generated in ascending or descending order a sequence of numeric values according to the specification with which the sequence was created. It can be configured to restart generating sequence numbers again when exhausted (reaches the maximum value).  The range of values depends on the data type we are declaring the sequence.

Sequence is similar to identity column. But, there are many differences between them. You can find the comparisons in the following table –

Sno	Identity	Sequence
1	Identity is a table property.	User-defined Object.
2	It is associated to a column of a table.	Can be used in multiple tables. In other words, a single series of numbers can be shared between multiple tables and also can be shared between multiple columns of the same table.
3	It is generated when rows are inserted.	Next sequence number is generated before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table.
4		The NEXT VALUE FOR function can be used as the default value for a column in a table.
5	Cannot be restarted with the range of values.	It can be restarted generating sequence numbers when it reaches the maximum value.

Syntax for creating SEQUENCE in SQL Server: 

 

Arguments:

sequence_name              :  User-Defined Name for the sequence

[ built_in_integer_type | user-defined_integer_type ]                :

A Sequence can be defined as any integer type. Following are the types can be used to define a sequence –

tinyint : Range 0 to 255

smallint               : Range -32,768 to 32,767

int                          : Range -2,147,483,648 to 2,147,483,647

bigint                    : Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

decimal and numeric with a scale of 0.

Any user-defined data type (alias type) that is based on one of the allowed types.

If the data type is not defined while creating the sequnece, Since this is optional, SQL server will create it with the default data type as bigint.

[ START WITH <constant> ]         :

This start value should be greater than or equal to minimum value and less than or equal to maximum value. Sequence number will be starting from this number. Default start value is the minimum value for ascending sequence objects and maximum value for the descending values.

[ INCREMENT BY <constant> ]    :

                This is used to increment or decrement value of the sequence object. This value is passed to NEXT VALUE FOR function for each call. If we want to decrement the values we have to give the negative value here. It cannot be 0(zero). This is optional and it will take 1 as default value.

[ { MINVALUE [ <constant> ] } | { NOMINVALUE } ]          :

                This specifies the boundary value for the sequence object. This is an optional property. If we are not specifying this, it will take the minimum value of the data type for the sequence object.

[ { MAXVALUE [ <constant> ] } | { NOMAXVALUE } ]        :

                This specified the other boundary value for the sequence object. This is an optional property. If we are not specifying this, it will take the maximum value of the data type of the object.

[ CYCLE | { NOCYCLE } ]  :

                This property specifies whether the sequence should restart from the minimum value or not(in case of ascending Sequence). If we specify CYCLE, once it reaches the maximum value, it will be restarted from the minimum value again. By default it is NOCYCLE. In this case, after reaching the maximum value, it throws an exception.

[ { CACHE [ <constant> ] } | { NO CACHE } ]           :

                This property is used to increase the performance of SEQUENCE object. This is helpful when we are inserting bulk records in a single shot. In this case, for every record NEXT VALUE FOR function is fired, generates a number and that value will be returned back to SQL. This degrades the performance of SQL. Instead of this multiple switches, we can improve the performance by generating set of values at the first invocation of the NEXT VALUE FOR function and keep it in memory and give instructions to SQL Server to use them. If there is any improper shutdown of the server, we will lose the unused numbers. This is disadvantage of this object. By default, it is NO CACHE.

I better leave this here and discuss in my next post.

Information schema view for querying the sequences is – sys.sequences.

Let us see how to use this sequence with examples.

Creating a sequence with Default Values:

SQL Server will take the default values for the properties, if we are not giving the options of sequence while creating it.

The statement below creates the sequence in ‘test’ schema.

CREATE SEQUENCE test.Test_Def_Seq

You can issue below query to know about the sequence.

SELECT * FROM [master].[sys].[sequences] where name='Test_Def_Seq'

You can see all the default values for the properties of Sequence.

Creating a sequence with user defined Properties:

Let us create a sequence which starts with 1 till 100 with the interval as 1 with cache of 10 numbers and cycle. Here is the query -

CREATE SEQUENCE test.Test_Seq

AS INT

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 100

CYCLE

CACHE 10;

In this case sequence will start from 1 till 100 with the interval of 1. Once it reaches the maxvalue which is 100, it again start from minvalue which is 1. And it will generate 10 numbers and keep it in the memory.

Here is the query to get the next value from the sequence object.

SELECT NEXT VALUE FOR Test.Test_SEQ

Output is : 1

When you issue the above SQL statement, everytime it will go to information schema’s sequences tables to get the current_Value and increment value for Test_SEQ sequence, adds both of them and the result will be returned from the NEXT VALUE FOR function.

To create the sequence object, user should have the CREATE SEQUENCE privilege and DROP SEQUENCE to drop the sequence.

Here is the SQL to Drop the Sequence –

DROP SEQUENCE <Sequence_Name>

Query to drop Test_Seq sequence is –

DROP SEQUENCE Test.Test_SEQ;

Let us discuss more about the caches and the usage of Sequences in SQL statements in next post.

All the best and Happy Learning.