Introduction to Sequences in SQL 2012

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;

GO

Where n,m can be any positive of negative integer

OR

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’);

           Identity Sequence
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

Leave a Reply