Articles → SQL SERVER → Sequence Objects In SQL Server
Sequence Objects In SQL Server
Purpose
How The Sequence Objects Are Different From The Identity Column?
Syntax
CREATE SEQUENCE [schema].[Name_of_Sequence]
[ AS <data type> ]
[ START WITH <value> ]
[ INCREMENT BY <value> ]
[ MINVALUE <value >]
[ MAXVALUE <value>]
| Parameter | Description |
|---|
| Data type | Specifies the data type of the sequence. Data types can be Decimal, Int, SmallInt, TinyInt, and BigInt |
| START WITH | Sets the starting value for the sequence object |
| INCREMENT BY | Sets the amount that you want your sequence object to increment by |
| MIN VALUE | This is an optional parameter that specifies the minimum value for the sequence object |
| MAX VALUE | This is an optional parameter that sets the maximum value for the sequence object |
Example
CREATE SEQUENCE [dbo].[MyCounter]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
Get Next Value Of The Sequence
SELECT NEXT VALUE FOR [dbo].[MyCounter]
Altering The Sequence
ALTER SEQUENCE [MyCounter]
MAXVALUE 25