Introduction
Default values
How to auto-generate values in a database
16. SEQUENCE—explanation
Summary

Instruction

Very nice! SQL Server provides one more way to generate unique integer values: sequence. A sequence is a database object, similar to IDENTITY. It generates unique consecutive integers. See the example:

CREATE SEQUENCE author_seq
  START WITH 1
  INCREMENT BY 1;

We can get an integer value from the author_seq sequence using this:

SELECT NEXT VALUE FOR author_seq;

If this is the first time we've used author_seq, the result will be 1. The default start value for sequence is 1, and the default increment value is 1. Running the query above a second time returns 2, a third time returns 3, and so on.

The creator of the sequence can set the start or increment value to another value, even a negative one. For example, if we start with -120 and increment by -2, we'd get -120, -122, -124, etc.

In SQL Server, IDENTITY is more commonly used than SEQUENCE, but in some situations, sequence is the better solution.

Exercise

Run the sample query in the code editor to see how a sequence generates a new value.