Sequences come new with the SQL Server 2012 and finally include ANSI standard for generating numerical sequences, which had been missing in the SQL Server until now. Neither IDENTITY nor other various custom solutions were fully able to replace it. Sequences are independent objects, which we can created on the level of a database scheme and are neither assigned to a table nor to a column, as was the case with IDENTITY up until now. Their main purpose is to generate numerical values in ascending or descending order and at intervals defined upon their creation. For generating new values, the sequences are referenced directly by means of an application or database code. The article will closely show you how sequences are created, which set up the sequences have and how to obtain new values. We will also think about whether they can be considered as a full-fledged replacement of IDENTITY from the database developer’s point of view and what it would mean to replace it by sequences in the existing IDENTITY database, in this case.
[toc]
1. Creating Sequences
Sequences are created similarly as other database objects with using of the commands: CREATE SEQUENCE and are removed with the commands: DROP SEQUENCE. The same is true for their change, which is done with ALTER SEQUENCE.
The syntax of the CREATE SEQUENCE command is as follows:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
[schema_name].[sequence_name]
- The same rules apply for the name of a sequence as for other database objects. Sequences always belong to only one database scheme and their name has to be unique within the scheme. Since it is dealing with independent database objects, we can apply security settings equal to the scheme compared to IDENTITY, where no explicit assignment of rights was possible.
AS [ built_in_integer_type | user-defined_integer_type ]
- Each sequence has its own system or user data type and it can only deal with some of these numerical data types:
- tinyint – from 0 to 255
- smallint – from -32,768 to 32,767
- int – from -2,147,483,648 to 2,147,483,647
- bigint – from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
- decimal and numeric, but only with zero count tenths decimal places
- User data types established on the above mentioned system types.
- The initial data type is bigint. It is good to keep this is mind when creating sequences because in most cases the range of this data type will not be necessary and it is more beneficial to select the lowest data type possible.
START WITH <constant>
- Determines this first value, which the sequence will return. It is an extremely important setting because when it is not used, the first returned value will be the minimum or maximum value of the used data type according to whether it is dealing with an ascending or descending sequence. The value must obviously be within the range of the selected data type.
INCREMENT BY <constant>
- The value determining the size of the increment or decrement upon every calling of the function NEXT VALUE FOR, which obtains another value from the sequence. The value cannot be 0 and the initial setting is 1. The value can also be negative, the same as for IDENTITY.
MINVALUE <constant> | NO MINVALUE
- Determines the minimum value, which will be generated by the given sequence. If it is not determined, the minimum value is always the lowest value of the used data type, possibly 0 for tinyint.
MAXVALUE <constant> | NO MAXVALUE
- Determines the top limit of the sequence. If it is not set, the top limit is the maximum value of the data type.
CYCLE | NO CYCLE
- This characteristic determines whether the sequence should again start generating from the beginning once it has reached the maximum value for an ascending sequence or a minimum value for a descending sequence. The initial setting is NO CYCLE and upon reaching the limit maximum value, an exception is generated. We cannot forget that if a renewal of the cycle takes place, the sequence re-starts from the minimum or maximum values, not from the START WITH values.
CACHE [<constant> ] | NO CACHE
- Serves to optimize speed of generating other sequence values by minimizing the number of disc operations. The initial setting is CACHE and the size is set to 50, even though this value is not officially documented and can later be changed without prior warning. Therefore, it is always better to specify the explicit value.
- The SQL Server does not retain the whole range of individual values in its memory. It only retains two numbers: the current value and the number of free values remaining in the cache. Later, we will see how exactly cache works.
2. Basic Sequence
Little is needed for creating a simple sequence: run the following command: CREATE SEQUENCE with at least the basic setting:
CREATE SEQUENCE dbo.MySequence
AS INT
START WITH 1
INCREMENT BY 1 ;
GO
Our new sequence is the closest to that, which we know as IDENTITY(1,1). The start value is 1 and the increment is also 1. We only determined the data type, INT, because the initial data type, BIGINT, is unnecessarily too large for most scenarios. Therefore, our sequence corresponds with the following use of IDENTITY when defining the columns: ID INT NOT NULL IDENTITY(1,1).
We can view the sequences in Management Studio and if necessary, modify its settings:
After right clicking on Properties, details about the sequence, including the possibility to restart it, will appear:
The same as other objects, sequences also have their own management view (DMV), where we can find their list and configuration details:
SELECT * FROM sys.sequences
The only thing which we are not able to find out from sys.sequences, are the current values of the CACHE setting. It is possible to find these out using the following system tables but only when we connect to the instance as an administrator using a Dedicated Administrator Connection (DAC):
SELECT * FROM sys.sysschobjs WHERE id = OBJECT_ID('dbo.MySequence')
SELECT * FROM sys.sysobjvalues WHERE objid = OBJECT_ID('dbo.MySequence')
To find out how exactly to read the displayed information and to find out more about the internal cache management of sequences, click here.
3. Generating New Values
If we already have a sequence created, it is time to show you how to call it and how to obtain new values for further use.
There are two possibilities of generating new values:
- If we want to obtain a single value (SELECT NEXT VALUE)
- If we want to obtain values in a specific range (sys.sp_sequence_get_range)
SELECT NEXT VALUE FOR <sequence>
Using this clause, we will obtain a new value for every calling from the sequence:
SELECT NEXT VALUE FOR dbo.MySequence AS NewSeqValue SELECT NEXT VALUE FOR dbo.MySequence AS NewSeqValue SELECT NEXT VALUE FOR dbo.MySequence AS NewSeqValue SELECT NEXT VALUE FOR dbo.MySequence AS NewSeqValue SELECT NEXT VALUE FOR dbo.MySequence AS NewSeqValue GO
We see that for every calling SELECT NEXT VALUE FOR we obtained another value from our sequence.
sys.sp_sequence_get_range
With the aid of the sys.sp_sequence_get_range system procedure, we can ask for more values at once, which can for example be very useful for applications, which process a large amount of data and simultaneously run the insertion of new rows into the database in more threads. Each thread can for example request a calling of a procedure by another 50 sequence values and the range which it obtains does not overlap with ranges, which were generated for other threads.
DECLARE @FirstSeqNum SQL_VARIANT, @LastSeqNum SQL_VARIANT, @CycleCount INT, @SeqIncr SQL_VARIANT, @SeqMinVal SQL_VARIANT, @SeqMaxVal sql_variant EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.MySequence', @range_size = 10, @range_first_value = @FirstSeqNum OUTPUT, @range_last_value = @LastSeqNum OUTPUT, @range_cycle_count = @CycleCount OUTPUT, @sequence_increment = @SeqIncr OUTPUT, @sequence_min_value = @SeqMinVal OUTPUT, @sequence_max_value = @SeqMaxVal OUTPUT SELECT @FirstSeqNum AS FirstVal, @LastSeqNum AS LastVal, @CycleCount AS CycleCount, @SeqIncr AS SeqIncrement, @SeqMinVal AS MinSeq, @SeqMaxVal AS MaxSeq GO 3
Using the GO 3 trick, we called the procedure a total of three times and from the results we can see that we don’t obtain a list of generated values, therefore, 3 * 10 rows but only 3 * one row, which tells us the first and last value of our range (FirstVal, LastVal) and the increment (SeqIncrement), which is sufficient for us to be able to finish calculating the individual values. CycleCount together with MinSeq and MaxSeq provides us with valuable information in case the obtained range appears on the edge, when the sequence automatically restarts from the beginning.
4. Cyclical Sequences
With the CYCLE parameter, we can determine that following the exhaustion of its range, the sequence automatically restarts from the minimum value, which is either the lowest value of the data type or a value determined using the MINVALUE configuration.
The following script illustrates a simple example of cyclical behavior:
CREATE SEQUENCE dbo.MySequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 3 CYCLE GO SELECT NEXT VALUE FOR dbo.[MySequence] SeqValue, name FROM [sys].[sysobjects] GO
We created a dbo.MySequence sequence and we set a MINVALUE = 1 and MAXVALUE = 3 and ran it with CYCLE. In the test inquiry, we see that the sequence is continuously repeating and therefore, we can for example use it as a similar function NTILE().
It is important to not forget to state the MINVALUE setting because otherwise the sequence will restart from the minimum value of the data type:
CREATE SEQUENCE dbo.[MySequence] AS INT START WITH 1 INCREMENT BY 1 -- MINVALUE 1 MAXVALUE 3 CYCLE GO SELECT NEXT VALUE FOR dbo.[MySequence] SeqValue, name FROM [sys].[sysobjects] GO
5. Restart Sequence
Apart from the sequence’s automatic restart using CYCLE, we can restart the sequence from the beginning with the following command:
ALTER SEQUENCE dbo.MySequence RESTART WITH 1 GO
If we assume WITH 1, the sequence will restart from the value determined upon its creation.
6. OVER()
The OVER() clause enables us to determine the order in which the sequence values will be generated in relation to the individual rows of the data set.
CREATE SEQUENCE dbo.MySequence
START WITH 1
INCREMENT BY 1
GO
SELECT NEXT VALUE FOR dbo.MySequence OVER(ORDER BY Name), name
FROM [sys].[sysobjects]
GO
The clause can seem useless but only until we encounter one of the unpleasant limitations of sequences: we cannot use them in queries together with TOP, ORDER and OFFSET, as is illustrated in this example:
SELECT NEXT VALUE FOR dbo.MySequence SeqValue, name FROM sys.sysobjects ORDER BY name GO
7. Transactions
The same as for IDENTITY, sequences are also generated out of the context of the transaction and ROLLBACK leads to loss of the generated values:
SELECT NEXT VALUE FOR dbo.MySequence SeqValue GO BEGIN TRAN SELECT NEXT VALUE FOR dbo.MySequence SeqValue ROLLBACK GO SELECT NEXT VALUE FOR dbo.MySequence SeqValue GO
8. Sequence as DEFAULT Value of a column
The NEXT VALUE FOR clause can be used when creating a table as a DEFAULT value of the column, which presents the answer to the question – whether it is possible to replace the traditional use of IDENTITY as a primary table key, with the aid of a sequence. We are simultaneously able to reach a behavior which is not feasible using IDENTITY:
CREATE TABLE dbo.TableA
( ID INT DEFAULT NEXT VALUE FOR dbo.MySequence PRIMARY KEY,
Value NVARCHAR(20)
);
CREATE TABLE dbo.TableB
( ID INT DEFAULT NEXT VALUE FOR dbo.MySequence PRIMARY KEY,
Value NVARCHAR(20)
);
INSERT INTO dbo.TableA ( [Value] )
VALUES ('A'), ('B')
INSERT INTO dbo.TableB ( [Value] )
VALUES ('C'), ('D')
INSERT INTO dbo.TableA ( [Value] )
VALUES ('E'), ('F')
GO
SELECT * FROM dbo.TableA
SELECT * FROM dbo.TableB
GO
SELECT * FROM dbo.TableA
UNION ALL
SELECT * FROM dbo.TableB
ORDER BY ID
GO
The highlighted rows in the script indicate that we used our dbo.MySequence sequence as an initial value for the ID column. We used the sequence simultaneously in two tables, which enables us to have two non-overlapping values when inserting data into both tables. Without sequences with similar behavior, it has to either be implemented with the aid of a complicated logic or by a third table, which has an IDENTITY column and is a source of new values.
9. INSERT
We can use the sequences directly in the INSERT command for inserting new values:
CREATE TABLE dbo.TableA ( ID INT PRIMARY KEY, Value NVARCHAR(20) ); INSERT INTO dbo.TableA ( [ID], [Value] ) VALUES (NEXT VALUE FOR dbo.MySequence, 'A'), (NEXT VALUE FOR dbo.MySequence, 'B') GO INSERT INTO dbo.TableA ( [ID], [Value] ) SELECT NEXT VALUE FOR dbo.MySequence, 'C' GO SELECT * FROM dbo.TableA GO
10. Limitations
For using sequences, a number of limitations exist. Even though the majority of them can be avoided by another syntax record or the query logic, they are not always pleasant and it is necessary to keep them in mind.
Sequences cannot be used:
- in view, functions and calculated columns
- as a parameter table-valued function
- as an argument of aggregate functions (SUM(), …)
- in conditional expressions when using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF
- in FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY, or FOR XML clauses
- if the database is in the read-only mode
- in subqueries including common table expressions and derived tables
- in the MERGE command (except if it is inserted into tables using MERGE, where the sequence is used as a DEFAULT)
- as a check or as a rule
- as a default in the user table type
- in row-constructor (VALUES), if it is not directly a part of INSERT
- in the WHERE clause
- together with TOP, OFFSET, ORDER clauses
Examples:
UNION (ALL)
SELECT NEXT VALUE FOR dbo.MySequence, * FROM sys.[columns] UNION ALL SELECT NEXT VALUE FOR dbo.MySequence, * FROM sys.[columns] GO
We can avoid an Error using this alternative syntax.
SELECT NEXT VALUE FOR dbo.MySequence SEQ, * FROM ( SELECT * FROM sys.[columns] UNION ALL SELECT * FROM sys.[columns] ) a GO
ISNULL()
DECLARE @i INT SELECT ISNULL(@i,(NEXT VALUE FOR dbo.MySequence)) GO
Alternative syntax:
DECLARE @i INT DECLARE @SEQ INT SET @SEQ = NEXT VALUE FOR dbo.[MySequence] SELECT ISNULL(@i, @SEQ) GO
11. Bugs
The KB3011465 error is known and worth mentioning. This is where the generation of duplicate sequence values can occur in the SQL Server 2012 and 2014 versions. This can occur if instance is currently working under memory pressure.