Sequences
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:-
CREATE SEQUENCE dbo.Sequence_name AS [ sqol-defined_integer_type OR user-defined_integer_type ] START WITH <constant> INCREMENT BY <constant> MINVALUE <constant> OR NO MINVALUE MAXVALUE <constant> OR NO MAXVALUE CYCLE CACHE <constant> OR NO CACHE;
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.
Example:-
Below is CREATE Sequence example
CREATE SEQUENCE dbo.TestSQLSequence AS BIGINT START WITH 8 INCREMENT BY 1 MINVALUE 2 MAXVALUE 9 CYCLE CACHE 10;
Below is DROP
Sequence example
DROP SEQUENCE dbo.TestSQLSequence
Argument 'START WITH'
cannot be used in an ALTER SEQUENCE
statement. RESTART WITH is used in
Place of start with.
Alter SEQUENCE dbo.TestSequence RESTART WITH 2 INCREMENT BY 1 MINVALUE 2 MAXVALUE 9 CYCLE CACHE 10; GO
Now Create table
Create Table Account ( ID INT, Name NVARCHAR(100) NOT NULL, DeptId INT NOT NULL )
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.
INSERT Account (ID, Name,DeptId) VALUES ( NEXT VALUE FOR TestSQLSequence, 'KPO', 1 ); INSERT Account (ID, Name,DeptId) VALUES ( NEXT VALUE FOR TestSQLSequence, 'BPO', 1 ); INSERT Account (ID, Name,DeptId) VALUES ( NEXT VALUE FOR TestSQLSequence, 'VIP', 2 ); INSERT Account (ID, Name,DeptId) VALUES ( NEXT VALUE FOR TestSQLSequence, 'CEO', 3 );
No comments:
Post a Comment
Note: only a member of this blog may post a comment.