SEQUENCE Basics

The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. Unlike IDENTITY, which is a table property, SEQUENCE is an independent schema bound object. Different tables and objects can share the same SEQUENCE object. Below is the syntax of CREATE 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}]
[;]

A sequence can be defined as any integer type, default is bigint.
START WITH specifies the first value returned by the sequence object, which must be within the minimum and maximum values, default is the minimum value if its an ascending sequence and the maximum if its an descending sequence.
INCERMENT BY specifies the value used to increment (or decrement if negative) the value of the sequence for each call to the NEXT VALUE FOR. If the increment is positive, the sequence is ascending; if the increment is negative, the sequence is descending. The default is 1.
MINVALUE specifies the minimum value. The default is the minimum value of the data type.
MAXVALUE specifies the maximum value. The default is the maximum value of the data type.
CYCLE specifies whether the sequence should restart from the minimum value (or maximum for descending sequence) or throw an exception when its minimum or maximum value is exceeded. The default is NO CYCLE. Note that cycling restarts from the minimum or maximum value, not from the start value.
The CACHE option is used to increase the performance of a sequence object by minimizing the number of disk IOs that are required to generate sequence numbers. If you didn't specify any CACHE nor NO CACHE when creating a sequence, SQL Server will take care it for you. I am not going to explain too much about it, further details are provided in the BOL.
Let's create a simple sequence and play with it.
CREATE SEQUENCE Simple_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NO CYCLE;
Then try to get values from it a few times...
SELECT NEXT VALUE FOR Simple_Seq;
GO 9
SELECT NEXT VALUE FOR Simple_Seq;
After you hit 9 and then invoke the next value, you will get this error message.
Msg 11728, Level 16, State 1, Line 1
The sequence object 'Simple_Seq' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
You can restart the sequence by using the ALTER SEQUENCE statement:
ALTER SEQUENCE Simple_Seq RESTART;
Also if you specified the CYCLE option when creating the sequence, the sequence number will be recycled by itself.
CREATE SEQUENCE Cycle_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
CYCLE;
SELECT NEXT VALUE FOR Cycle_Seq;
GO 9
SELECT NEXT VALUE FOR Cycle_Seq;

As I said, a SEQUENCE is an independent schema bound object which can be shared by different tables. Below two tables both use the same sequence as the default value of its primary key.
-- restart the sequence
ALTER SEQUENCE Cycle_Seq RESTART
GO
CREATE TABLE A (
id int DEFAULT NEXT VALUE FOR Cycle_Seq PRIMARY KEY,
n VARCHAR(50)
);
GO
CREATE TABLE B (
id int DEFAULT NEXT VALUE FOR Cycle_Seq PRIMARY KEY,
n VARCHAR(50)
);
GO
INSERT A (n) VALUES ('mouse');
INSERT B (n) VALUES ('Metal');
INSERT A (n) VALUES ('cow');
INSERT A (n) VALUES ('tiger');
INSERT B (n) VALUES ('wood');
SELECT * FROM A
SELECT * FROM B

Result: