Creating Sequence object in SQL Server

     Sequence object is introduced to SQL Server server in 2012 version. It is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. We have the full control of creating the sequence numbers. Applications refer to a sequence object to get its next value.

     You can call NEXT VALUE FOR function to figure out what the next value is for a sequence. Sequences are not associated with specific tables and number are generated outside of the transactions.

built_in_integer_type can be tinyint, smallint, int, bigint, decimal and numeric with a scale of 0

If you don't provide a data type, bigint will be used as default.

• START WITH is the first value that sequence creates.
• INCREMENT BY value will be used to increment/decrement the current value of Sequence object.If the increment is a negative value, the sequence is descending.
• MINVALUE and MAXVALUE specifies the bounds for the sequence object.
• CYCLE | NOCYCLE properties specifies if the sequence object should restart from the MINVALUE or throw an exception when it reaches to MAXVALUE
• CACHE is for performance issues and it is optional, It minimizes the number of disk IOs that are required to generate a sequence numbers.

Examples

To get information about any Sequence object

To get the next value of an sequence object.