SEQUENCE in SQL Server

In SQL Server, a sequence is a database object used to generate a series of unique numeric values. Unlike an IDENTITY column, a sequence is independent of any specific table and can be shared across multiple tables or retrieved without inserting a row. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Unlike identity columns values that are generated when rows are inserted. This is introduced in SQL Server 2012.

Syntex of creating Sequence

CREATE SEQUENCE [schema_name.] sequence_name      [ AS 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 } ];

Let see the keywords.

Sequence_name  : Name of the Sequence and scheme where are creating it.

AS :  as may be INT or BIGINT, TINYINT, SMALLINT by default it id BIGINT.

START WITH: Specify the first value that the sequence returns. The start_value must be between the range (min_value, max_value). The start_value defaults to the min_value in an ascending sequence and max_value in a descending sequence.

INCREMENT BY: Specify the increment_value of the sequence object when you call the NEXT VALUE FOR function. If increment_value is negative, the sequence object is descending; otherwise, the sequence object is ascending. Note that the increment_value cannot be zero.

MINVALUE : Specifies the bounds for the sequence object. The default minimum value for a new sequence object is the minimum value of the data type of the sequence object. This is zero for the tinyint data type and a negative number for all other data types.

MAXVALUE: Specifies the bounds for the sequence object. The default maximum value for a new sequence object is the maximum value of the data type of the sequence object.

CYCLE | NO CYCLE : Property that specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects) or throw an exception when its minimum or maximum value is exceeded. The default cycle option for new sequence objects is NO CYCLE. Cycling a SEQUENCE restarts from the minimum or maximum value, not from the start value.

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

Let’s create a simple sequence

CREATE SEQUENCE dbo.OrderSeq     AS INT     START WITH 1     INCREMENT BY 1;

Sequence created successfully. We can see this the SQL Server by Navigating below

To access the value we will use

“NEXT VALUE FOR dbo.OrderSeq”.

 See the example

Sequence we can used in the insert.

CREATE TABLE Orders (     OrderID INT PRIMARY KEY,     OrderName VARCHAR(50) );  INSERT INTO Orders VALUES (NEXT VALUE FOR dbo.OrderSeq, 'Laptop');  SELECT * FROM Orders;

 Record inserted successfully.

We can add it as default constraint.

ALTER TABLE Orders ADD CONSTRAINT DF_OrderID DEFAULT NEXT VALUE FOR dbo.OrderSeq FOR OrderID;

No inserting record.