Sequence Objects

Sequence is a type of user-defined object that generates a sequence of numeric values. We can use a Sequence Object for T-SQL queries. A Sequence Object starts with a specific value. The value of a Sequence Object can be increase or decrease with a specific interval. Sequence Objects have an option for a minimum and maximum value. Applications refer to a Sequence Object to retrieve its next value. The relationship between sequences and tables is controlled by the application. Sequences, unlike identity columns, are not associated with specific tables.

Syntax

  1. CREATE SEQUENCE [schema_name . ] sequence_name
  2. [ AS [ built_in_integer_type | user-defined_integer_type ] ]
  3. [ START WITH <constant> ]
  4. [ INCREMENT BY <constant> ]
  5. [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
  6. [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
  7. [ CYCLE | { NO CYCLE } ]
  8. [ { CACHE [ <constant> ] } | { NO CACHE } ]
  9. [ ; ]
Arguments

sequence_name: define a unique name for a Sequence Object.

[ built_in_integer_type | user-defined_integer_type ]: defines the data type for a Sequence Object. A sequence can be defined as any integer type. If no data type is provided, the bigint data type is used as the default. The following types are allowed:

Data Type

Range

Tinyint 0 to 255
Smallint -32,768 to 32,767
Int -2,147,483,648 to 2,147,483,647
Bigint -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

START WITH: It is a type of constant. The first value returned by the Sequence Object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the Sequence Object.

INCREMENT BY: It is a type of constant. It can be either a positive or negative value. If value is a negative number then the value will be an ascending sequence else the value will be an descending sequence.

MINVALUE: It defines a minimum value allowed for the sequence. The default minimum value for a new Sequence Object is the minimum value of the data type of the Sequence Object.

MAXVALUE: It defines a maximum value allowed for the sequence. The default maximum value for a new Sequence Object is the maximum value of the data type of the Sequence Object.

[ CYCLE | NO CYCLE ]: Cycle means that the sequence will start over once it has completed the sequence. NO CYCLE means that the sequence will raise an error when it has completed the sequence. It will not start the sequence over again.

[ CACHE [<constant> ] | NO CACHE ]: Increases performance for applications that use Sequence Objects by minimizing the number of disk operations required to generate sequence numbers. Defaults to CACHE.

For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the Sequence Object.

Now for a better understanding we will consider some examples.

Example 1

  1. CREATE SEQUENCE Sequence_Example
  2. START WITH 1
  3. INCREMENT BY 1;
In the preceding example, we created a sequence with the name Sequemce_Example. In this example we don't define the data type for the sequence. So SQL Server uses the default datatype (in other words bigint). The starting value of the sequence is 1 . We don't define the minimum and maximum value for Sequence, so SQL Server will use the minimum and maximum value of datatype (in other words minimum = -9,223,372,036,854,775,808 , maximum = 9,223,372,036,854,775,807). We don't define anything about the Cache Option, so SQL Server uses the Cache option by default for the sequence.

Example 2
  1. CREATE SEQUENCE Sequence_Examle
  2. AS SMALLINT
  3. START WITH 1
  4. INCREMENT BY 1
  5. MINVALUE 1
  6. MAXVALUE 99
  7. NO CYCLE
  8. CACHE 10;
In the preceding example, we created a sequence with the name Sequence_Example. The data type of the sequence is a smallint. Sequence will start from 1 and each time it increases by 1 (in other words 2, 3, 4, 5…). Minimum value of sequence is 1 and maximum value is 99.

We define the cache size of 10, so it will cache the 10 value at a time to increase the performance. We use the No CYCLE option for sequence so the sequence will truncate after obtaining the maximum value.

Example 3
  1. CREATE SEQUENCE Sequence_Example
  2. AS SMALLINT
  3. START WITH 1
  4. INCREMENT BY 1
  5. MINVALUE 1
  6. MAXVALUE 99
  7. NO CYCLE
  8. CACHE 10;
  10. SELECT NEXT VALUE FOR Sequence_Example AS [sequence]
Output

see result

The “SELECT NEXT VALUE FOR Sequence_Object” statement is used for retrieving the next value from the Sequence Object.

Example 4
  1. CREATE SEQUENCE Sequence_Example
  2. AS SMALLINT
  3. START WITH 1
  4. INCREMENT BY 1
  5. MINVALUE 1
  6. MAXVALUE 99
  7. NO CYCLE
  8. CACHE 10;
  10. SELECT NEXT VALUE FOR Sequence_Example AS [sequence] UNION ALL
  11. SELECT NEXT VALUE FOR Sequence_Example UNION ALL
  12. SELECT NEXT VALUE FOR Sequence_Example UNION ALL
  13. SELECT NEXT VALUE FOR Sequence_Example
Output

message

This example illustrates that we cannot use Distinct, Union, Union All, Intersect and Except operator with a Sequence Object.

Example 5
  1. CREATE TABLE #TEMP
  2. (
  3. IID INT IDENTITY(1,1),
  4. SEQUENCE INT
  5. );
  7. DECLARE @INT INT;
  8. SET @INT=1;
  10. WHILE(@INT<=10)
  11. BEGIN
  12. INSERT INTO #TEMP
  13. VALUES
  14. (
  15. NEXT VALUE FOR Sequence_Example
  16. )
  18. SET @INT=@INT+1;
  19. END
  21. SELECT * FROM #TEMP t
  22. DROP TABLE #TEMP
Output

TEMP

This example shows how to insert the data into a table from a Sequence Object.

Example 6: Drop Sequence
  1. IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
  2. DROP SEQUENCE Sequence_Example;
This example shows that we can drop a Sequence Object using the Drop Command.

Example 7
  1. CREATE SEQUENCE Sequence_Example
  2. AS SMALLINT
  3. START WITH 1
  4. INCREMENT BY 1
  5. MINVALUE 1
  6. MAXVALUE 5
  7. NO CYCLE
  8. CACHE 5;
  10. CREATE TABLE #TEMP
  11. (
  12. IID INT IDENTITY(1,1),
  13. SEQUENCE INT
  14. );
  16. DECLARE @INT INT;
  17. SET @INT=1;
  19. WHILE(@INT<=10)
  20. BEGIN
  21. INSERT INTO #TEMP
  22. VALUES
  23. (
  24. NEXT VALUE FOR Sequence_Example
  25. )
  27. SET @INT=@INT+1;
  28. END
  30. SELECT * FROM #TEMP t
  31. DROP TABLE #TEMP
Output

result

If we execute the preceding code, then SQL Server will throw an error. Because a Sequence Object has a maximum value 5 and we are exceeding this value.

So, to overcome this problem we can use the Cycle option for a Sequence Object.

Example 8 (Sequence with Cycle Option)
  1. CREATE SEQUENCE Sequence_Example
  2. AS SMALLINT
  3. START WITH 1
  4. INCREMENT BY 1
  5. MINVALUE 1
  6. MAXVALUE 5
  7. CYCLE
  8. CACHE 5;
  10. CREATE TABLE #TEMP
  11. (
  12. IID INT IDENTITY(1,1),
  13. SEQUENCE INT
  14. );
  16. DECLARE @INT INT;
  17. SET @INT=1;
  19. WHILE(@INT<=10)
  20. BEGIN
  21. INSERT INTO #TEMP
  22. VALUES
  23. (
  24. NEXT VALUE FOR Sequence_Example
  25. )
  27. SET @INT=@INT+1;
  28. END
  30. SELECT * FROM #TEMP t
  31. DROP TABLE #TEMP
Output

SELECT

In this example we define the maximum value equal to 5 and the Sequence Object contains a Cycle option so the Sequence Object will restart after reaching the maximum value and the values will repeat after a definite interval.

Example 9 (Restart Sequence)
  1. IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
  2. DROP SEQUENCE Sequence_Example;
  4. CREATE SEQUENCE Sequence_Example
  5. AS SMALLINT
  6. START WITH 1
  7. INCREMENT BY 1
  8. MINVALUE 1
  9. MAXVALUE 5
  10. CYCLE
  11. CACHE 5;
  13. CREATE TABLE #TEMP
  14. (
  15. IID INT IDENTITY(1,1),
  16. SEQUENCE INT
  17. );
  19. DECLARE @INT INT;
  20. SET @INT=1;
  22. WHILE(@INT<=10)
  23. BEGIN
  24. IF @INT%4=0
  25. BEGIN
  26. ALTER SEQUENCE Sequence_Example /* Restart Sequence */
  27. RESTART;
  28. END
  29. INSERT INTO #TEMP
  30. VALUES
  31. (
  32. NEXT VALUE FOR Sequence_Example
  33. )
  35. SET @INT=@INT+1;
  36. END
  38. SELECT * FROM #TEMP t
  39. DROP TABLE #TEMP
Output

Restart Sequence

This example shows that we can restart a Sequence Object at any time. To restart the current value of a Sequence Object to its initial or specified value, use the ALTER SEQUENCE statement.

Example 10
  1. IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
  2. DROP SEQUENCE Sequence_Example;
  4. CREATE SEQUENCE Sequence_Example
  5. AS SMALLINT
  6. START WITH 1
  7. INCREMENT BY 5
  8. MINVALUE 1
  9. MAXVALUE 50
  10. CYCLE
  11. CACHE 5;
  13. CREATE TABLE #TEMP
  14. (
  15. IID INT IDENTITY(1,1),
  16. SEQUENCE INT
  17. );
  19. DECLARE @INT INT;
  20. SET @INT=1;
  22. WHILE(@INT<=10)
  23. BEGIN
  24. IF @INT%4=0
  25. BEGIN
  26. ALTER SEQUENCE Sequence_Example /* Restart with a specific value */
  27. RESTART WITH 10 ;
  28. END
  29. INSERT INTO #TEMP
  30. VALUES
  31. (
  32. NEXT VALUE FOR Sequence_Example
  33. )
  35. SET @INT=@INT+1;
  36. END
  38. SELECT * FROM #TEMP t
  39. DROP TABLE #TEMP
Output

LTER SEQUENCE

This example shows that we can restart any Sequence Object with a specific value. In the preceding example we can see that when the Sequence Object restarts, it's start value will be 10 each time.

Example 11 (Sequence with descending order)
  1. IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
  2. DROP SEQUENCE Sequence_Example;
  4. CREATE SEQUENCE Sequence_Example
  5. AS SMALLINT
  6. START WITH 10
  7. INCREMENT BY -1
  8. MINVALUE 1
  9. MAXVALUE 50
  10. CYCLE
  11. CACHE 5;
  13. CREATE TABLE #TEMP
  14. (
  15. IID INT IDENTITY(1,1),
  16. SEQUENCE INT
  17. );
  19. DECLARE @INT INT;
  20. SET @INT=1;
  22. WHILE(@INT<=10)
  23. BEGIN
  24. INSERT INTO #TEMP
  25. VALUES
  26. (
  27. NEXT VALUE FOR Sequence_Example
  28. )
  30. SET @INT=@INT+1;
  31. END
  33. SELECT * FROM #TEMP t
  34. DROP TABLE #TEMP
Output

DROP TABLE

Example 12 (Sequence Information)

Using system view sys.sequences we can retrieve information about any sequence. A sys.sequences view can obtain the following information about the Sequence Object.

Sequence Information

The syntax to a view and the properties of a sequence in SQL Server (Transact-SQL) is:
  1. SELECT s.name ,s.type ,s.minimum_value,s.maximum_value,s.create_date,s.modify_date
  2. FROM sys.sequences s
  3. WHERE name = 'Sequence_Example';
Output

Output