Sequences in SQL Server

User can create an automated field using sequences. A sequence is an object used to generate a number sequence.

1. Create a Sequence in SQL Server

Syntax

CREATE SEQUENCE [schema] sequence_name
AS datatype
START WITH value
INCREMENT BY value
MINVALUE value | NO MINVALUE
MAXVALUE value |NO MAXVALUE
CYCLE | NO CYCLE
CACHE value | NO CACHE


Where,
  • datatype: It can be BIGINT, INT, TINYINT, SMALLINT, DECIMAL, or NUMERIC. If datatype is not specified the sequence will default to BIGINT datatype.
  • START WITH: It is a starting value.
  • INCREMENT BY: It may be a positive or negative value. If a positive value is specified then the sequence will be an ascending sequence of values. If a negative value is specified then the sequence will be descending sequence of values.
  • MINVALUE: The minimum value is allowed for the sequence.
  • NO MINVALUE: It means, no minimum value is specified for the sequence.
  • MAXVALUE: The maximum value is allowed for the sequence.
  • NO MAXVALUE: Specifies that no maximum value is specified for the sequence.
  • CYCLE: Specifies that, the sequence will start over once it has completed the sequence.
  • NO CYCLE: Specifies that, the sequence will report an error when it has completed the sequence.
  • CACHE VALUE: Caches the sequence to minimize disk IO.
  • NO CACHE: Doesn't cache the sequence number.

Example: Illustration of sequences in SQL Server.

CREATE SEQUENCE Stud_ID
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
NO CYCLE
NO CACHE


The above Query can be simply written as:

CREATE SEQUENCE Stud_ID
START WITH 1
INCREMENT BY 1;


Note: Once the sequence has been created in SQL Server, the user can view the properties of the sequence by using the following syntax:

SELECT * FROM sys.sequences
WHERE name = 'Sequence_name'

2. DROP Sequence in SQL Server

Syntax:

DROP SEQUENCE sequence_name

Example:

DROP SEQUENCE Stud_ID
▲