Best Explanation of Sequences in SQL Server
Sequence is introduced in SQL Server 2012. Sequence is a user-defined object that creates a sequence of a number. It is similar to an identity column.
Characteristics Of Sequences
- It is independent from table
- New value can be obtain before an INSERT statement
- New values can be generate in an UPDATE statement
- For performance point of view, you can set cache size option and also you can define min and max values, can allow cycling
- You can alter current value, increment, min value, max value, cycle and cache size properties of a sequence object besides the data type.
Creating Sequence Syntax:-
START WITH: Set Starting number in the sequence
INCREMENT BY: Set incrementing value of the sequence
MINVALUE: Set minimum value for sequence can produce.
MAXVALUE: Set maximum value for sequence can produce.
CYCLE: If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.
CACHE: If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.
Below is CREATE Sequence example
Below is DROP Sequence example
Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement. RESTART WITH is used in Place of start with.
Now Create table
Now Use created Sequence object "dbo.TestSQLSequence" to get the value by using NEXT VALUE FOR
The new NEXT VALUE FOR T-SQL keyword is used to get the next sequential number from a Sequence.
Best Explanation of Sequences in SQL Server Reviewed by Alok Singh on 23:39 Rating: