SEQUENCE () for SQL 2012

SEQUENCE ()

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.

Syntax:

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 } ]
[ ; ]

Examples
CREATE SEQUENCE txtSequence
START WITH 100
INCREMENT BY 5;

SELECT * FROM sys.sequences WHERE name='txtSequence'

Suppose I am going to Modifies the arguments of an existing sequence object. Then

ALTER SEQUENCE txtSequence
RESTART WITH 1
INCREMENT BY 5MINVALUE 1
MAXVALUE 50CYCLE
CACHE 3
;   

We should need ALTER permission on the sequence or ALTER permission on the schema. Then only we can able to modified the sequence.

To you want sequence value for the table, then using NEXT VALUE FOR function.
NEXT VALUE FOR used to generates a sequence number from the specified sequence object.

SELECT NEXT VALUE FOR txtSequence

Now, I am going to DROP the sequence, then

DROP SEQUENCE txtSequence