Post
Ask Question

Sequence Objects in SQL Server

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;  
  9.   
  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;  
  9.   
  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. );  
  6.   
  7. DECLARE @INT INT;  
  8. SET @INT=1;  
  9.   
  10. WHILE(@INT<=10)  
  11. BEGIN  
  12. INSERT INTO #TEMP  
  13. VALUES  
  14. (  
  15. NEXT VALUE FOR Sequence_Example  
  16. )  
  17.   
  18. SET @INT=@INT+1;  
  19. END  
  20.   
  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;  
  9.   
  10. CREATE TABLE #TEMP  
  11. (  
  12. IID INT IDENTITY(1,1),  
  13. SEQUENCE INT  
  14. );  
  15.   
  16. DECLARE @INT INT;  
  17. SET @INT=1;  
  18.   
  19. WHILE(@INT<=10)  
  20. BEGIN  
  21. INSERT INTO #TEMP  
  22. VALUES  
  23. (  
  24. NEXT VALUE FOR Sequence_Example  
  25. )  
  26.   
  27. SET @INT=@INT+1;  
  28. END  
  29.   
  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;  
  9.   
  10. CREATE TABLE #TEMP  
  11. (  
  12. IID INT IDENTITY(1,1),  
  13. SEQUENCE INT  
  14. );  
  15.   
  16. DECLARE @INT INT;  
  17. SET @INT=1;  
  18.   
  19. WHILE(@INT<=10)  
  20. BEGIN  
  21. INSERT INTO #TEMP  
  22. VALUES  
  23. (  
  24. NEXT VALUE FOR Sequence_Example  
  25. )  
  26.   
  27. SET @INT=@INT+1;  
  28. END  
  29.   
  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;  
  3.   
  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;  
  12.   
  13. CREATE TABLE #TEMP  
  14. (  
  15. IID INT IDENTITY(1,1),  
  16. SEQUENCE INT  
  17. );  
  18.   
  19. DECLARE @INT INT;  
  20. SET @INT=1;  
  21.   
  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. )  
  34.   
  35. SET @INT=@INT+1;  
  36. END  
  37.   
  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;  
  3.   
  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;  
  12.   
  13. CREATE TABLE #TEMP  
  14. (  
  15. IID INT IDENTITY(1,1),  
  16. SEQUENCE INT  
  17. );  
  18.   
  19. DECLARE @INT INT;  
  20. SET @INT=1;  
  21.   
  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. )  
  34.   
  35. SET @INT=@INT+1;  
  36. END  
  37.   
  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;  
  3.   
  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;  
  12.   
  13. CREATE TABLE #TEMP  
  14. (  
  15. IID INT IDENTITY(1,1),  
  16. SEQUENCE INT  
  17. );  
  18.   
  19. DECLARE @INT INT;  
  20. SET @INT=1;  
  21.   
  22. WHILE(@INT<=10)  
  23. BEGIN  
  24. INSERT INTO #TEMP  
  25. VALUES  
  26. (  
  27. NEXT VALUE FOR Sequence_Example  
  28. )  
  29.   
  30. SET @INT=@INT+1;  
  31. END  
  32.   
  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

 


Similar Articles