SQLServer 2012 introduces Sequence as new schema object which has been requested by the SQL community and due for release since many years.
- It is similar to sequence object of oracle database
- Unlike identity column sequence is not attached with table. It is separately created
- A sequence once created can be used with any table.
- Unlike identity columns values that are generated when rows are inserted, an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function.
- Sequences, unlike identity columns can generate numeric values in an ascending or descending order at a defined interval and can be configured to restart when ever needed.
- Sequence can also have a Data which is mostly any integer type but by default it is bigint.
How to create sequence?
CREATE SEQUENCE Test.CountBy1 START WITH n INCREMENT BY m;
Where n,m can be any positive of negative integer
CREATE SEQUENCE Temp.NumDesc AS decimal(3,0) START WITH 125
INCREMENT BY 25 MINVALUE 100 MAXVALUE 200 CYCLE;
How to use sequence?
To activate the sequence or to fetch the first value
SELECT NEXT VALUE FOR Temp.NumDesc
To fetch current value
SELECT current_value FROM Temp.NumDesc
To insert value in a column of a table using sequence
INSERT Test.TestTable(CounterColumn,Name) VALUES(NEXT VALUE FOR Test.CountBy1,’Samuel’);
|Defines as part of a table||It is not tied to the table|
|Auto generates values on insert||Value needs to be generated Explicitly|
|Can not restart the value||Can be restarted all over again|
|Value can not be Cached||Can be Cached|
|Same value cannot be used again||Same value can be used several times without regenerating new value|