Sql server 2012 sequence

Sql server 2012 sequence

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables. Sequence is popular concept in oracle where we can user for identity auto increment. Microsoft sql server 2012 introduces this concept. The following example given below

CREATE SEQUENCE  Employee_Seq

 AS INTEGER

 START WITH 1

 INCREMENT BY 1

 MINVALUE 1

 MAXVALUE 999999

 NO CYCLE;

You can call the sequence in ssms is given below

SELECT NEXT VALUE  Employee_Seq

Suppose when we have reached maximum value in sequence

ALTER SEQUENCE RESTART [WITH ];

Call the sequence insert statement

Insert into tblEmplyee (EmpId, Name ,Address.Salary) VALUES  (NEXT VALUE for   Employee_Seq ,'Anil',’this is tesging’,’120040’)