SEQUENCE IN SQL SERVER

In this article, we learn about the sequence.

SEQUENCE

• A sequence generates a sequence of unique integer values.
• A sequence has different orders like ascending or descending.
• We can view the sequence object under the Programmability →Sequences.

SYNTAX OF CREATING SEQUENCE

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH start_value]
[ INCREMENT BY increment_value ]
[ { MINVALUE [ min_value ] } | { NO MINVALUE } ]
[ { MAXVALUE [ max_value ]} | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ cache_size ] } | { NO CACHE } ]
[; ]

• The sequence name specifies a name to uniquely identify.
• Start with states the first value of sequence numbers.
• Increment by specifying the interval between sequence numbers.
• Min value states the minimum value of the sequence.
• Max value states the maximum value produced by the sequence.
• The cycle indicates the sequence is continued for generates the value starts from the min_value or max_value.
• Cache specifies the values to improve the performance.

EXAMPLE OF SEQUENCE

• In the following example, test_seq is a sequence name that incremented by 1, starts at 112, the max value is 120, and min value 112, does not cycle, and caches 7.

CREATE SEQUENCE test_seq INCREMENT BY 1

START WITH 112 MAXVALUE 120 MINVALUE 112

CYCLE CACHE 7

RESULT

• We can access the value help of CURVAL and NEXTVAL.
• The NEXTVAL returns the original value of the sequence.
• The CURVAL returns the current value which is the next value to the NEXTVAL.
• If the sequence is not yet used, the values of NEXTVAL and CURVAL are the same.

SELECT test_seq.NEXTVAL FROM dual

Output:- 112

SELECT test_seq.CURVAL FROM dual

Output:- 112

• In the below example, the NEXTVAL function generates a sequence number from the dual sequence object.
• When we execute the following statement again, then the value incremented by 1.

SELECT test_seq.NEXTVAL FROM dual

Output:- 113

ALTER SEQUENCE

• Syntax:- ALTER SEQUENCE <Seq_Name> variable value
• Example:- ALTER SEQUENCE test_seq MAXVALUE 125

DROP SEQUENCE

• Syntax:- DROP SEQUENCE <Seq_Name>
• Example:- DROP SEQUENCE test_seq

If you are new to database learning — SQL Server recommended is the following must-watch the video: -