Creating a Sequence Object
The Sequence object creation syntax is as follows, and the parameters usage is also covered.
Syntax:
CREATE SEQUENCE [Sequence_name]
AS <data type>
START WITH <initial_value>
INCREMENT BY <increment_value>
MINVALUE <min_value> | NO MINVALUE
MAXVALUE <max_value> | NO MAXVALUE
CYCLE | NO CYCLE
CACHE value | NO CACHE
The syntax works as follows
|
Parameter |
Description |
|---|---|
|
CREATE SEQUENCE |
Used to create a sequence followed by a database schema |
|
AS |
The data type of the Sequence can be Decimal, SmallInt, TinyInt, Int, and BigInt. Where BigInt is the default value. |
|
START WITH |
Initialize the starting value for the sequence |
|
INCREMENT BY |
Sets the amount by which you want to increment you sequence object |
|
MINVALUE |
Specifies the minimum value for sequence object, it is an optional parameter. |
|
MAXVALUE |
Specifies the maximum value for the sequence object, it is an optional parameter |
|
CYCLE |
It specifies whether the sequence should be restarted once it has reached its maximum or minimum value, it is an optional parameter |
|
CACHE |
Used to cache sequence object value, it is also an optional parameter with a default value of no-cache |
Sequence Objects in SQL Server
A Sequence is a user-created database Object that can be shared by multiple users to generate integers. In Simple words, we can say that Sequence objects are used to generate numeric values.
The Sequence Object was introduced to SQL Server with the rollout of SQL Server 2012.
Sequence Objects are similar to the IDENTITY column in any SQL Table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence Objects are used both independently and within the DML statements i.e. INSERT, DELETE and UPDATE. Can automatically generate unique numbers. It is a sharable object. Mainly used to create Primary Key values. 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.
List of Reasons why a Sequence object might be useful:
- Requirements call for more than one column in a table to be populated with a generated sequence number.
- There is a need to keep sequence numbers synchronized across multiple tables.
- Requires using a sequence number value to be stored in data type other than numeric.
- Need to have a nullable column but yet still be populated with a sequence number.
- An application requires the sequence number value to be obtained before a row is inserted into a table.
- want multiple sequence numbers to be acquired with code at the same time.
Contact Us