CREATE SEQUENCE (Transact-SQL) Introduced in SQL 2012

CREATE SEQUENCE (Transact-SQL) Introduced in SQL 2012

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. 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. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

Example

CREATE SEQUENCE [DBO].[ SequenceSampleExample] AS INT

 START WITH 10

 INCREMENT BY 1

 MINVALUE 10

 MAXVALUE 20

 NO CYCLE

GO

To execute

SELECT NEXT VALUE FOR  [DBO].[SequenceSampleExample]

It will display the next value of the object

For example this object(SequenceSampleExample) start with 10 and increment by 1,For first time the values is 10 and the second time the values is 11 like that it will increment up to max value

If we use no cycle mean the increment reaches the max values after raises error, if use cycle it will again increment start with min value

SELECT NEXT VALUE FOR  [DBO].[SequenceSampleExample]

Output: - 10

SELECT NEXT VALUE FOR  [DBO].[SequenceSampleExample]

Output: - 11

SELECT NEXT VALUE FOR  [DBO].[SequenceSampleExample]

Output: - 12

START WITH <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. The default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.

INCREMENT BY <constant>

Value used to increment (or decrement if negative) the value of the sequence object for each call to the NEXT VALUE FOR function. If the increment is a negative value, the sequence object is descending; otherwise, it is ascending. The increment cannot be 0. The default increment for a new sequence object is 1.

[ MINVALUE <constant> | NO 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 <constant> | NO 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.

Note that cycling 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.

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.

Steps to find the existing sequence objects in the DB

SELECT * FROM sys.objects o WHERE o.type='SO'

You will find the all the sequences in the DB

Drop the Sequence

DROP SEQUENCE <SEQUENCE name>