Sequence In SQL Server 2012

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.

Following are the characteristics of Sequence 

      Sequence is not associated with any table

      Application retrieves the next value

      Relationship between sequence and tables is controlled by User Application.

Sequence is a numeric Value which can be ascending /descending order.
   

Syntax for Sequence:

   CREATE SEQUENCE [schema_name . ] sequence_name

    [ AS [ built_in_integer_type | user-defined_integer_type]]

    [ START WITH <constant> ]

    [ INCREMENT BY <constant> ]

    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]

    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]

    [ CYCLE | { NO CYCLE } ]

    [ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

     START WITH starting value of Sequence

     INCREMENT BY Value Used to increment the value of sequnce

     MINVALUE Minimum Value of Sequence

     MAXVALUE Maximum Value of Sequence

     CYCLE/NOCYCLE Specifies whether sequence should restart from Minimum
      value when it exceeds the maximum value or not.

     CACHE/NOCACHE Minimizes the number of Disk I/o operations.

     

    

  Creating a Sequnce:

   Create SEQUENCE EX_Sequence

   START WITH 1

   INCREMENT BY 1

  Fetching the next Value from Sequence:

   SELECT NEXT VALUE FOR EX_Sequence

  

  

Sequences are internally stored in sys.sequences

 

   SELECT *

   FROM sys.sequences

   WHERE name = 'EX_Sequence' ;

       

     Or In SSMS Sequence can be found under
           Database->Programmability->Sequence

     Alter Sequence:

       Following the is the syntax to Alter a Sequnce

    ALTER SEQUENCE [schema_name. ] sequence_name

    [ RESTART [ WITH <constant> ] ]

    [ INCREMENT BY <constant> ]

    [ { MINVALUE <constant> } | { NO MINVALUE } ]

    [ { MAXVALUE <constant> } | { NO MAXVALUE } ]

    [ CYCLE | { NO CYCLE } ]

    [ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

      Example:

    ALTER SEQUENCE [dbo].[EX_Sequence]

    RESTART WITH 100

     Sequence will be restred from Value 100

    To Drop a sequence:

    Drop Sequence EX_Sequence