Creating and using sequences

 

Sequences are similar to identity columns in that they both generate unique values. However, sequences are objects that are independent of any tables. You can use sequences to generate values quickly and easily.

Sequences are not tied to a column in a table and are accessed separately. Additionally, they are not treated as any part of a transaction's unit of work.

You create a sequence using the CREATE SEQUENCE statement. For an example similar to the identity column example, create a sequence ORDER_SEQ:

CREATE SEQUENCE ORDER_SEQ START WITH 500

INCREMENT BY 1

MAXVALUE 1000

CYCLE

CACHE 24

This sequence is defined with a starting value of 500, incremented by 1 for every use, and recycles when the maximum value is reached. In this example, the maximum value for the sequence is 1000. When this value reaches 1000, it will restart at 500 again.

After this sequence is created, you can insert values into a column using the sequence. For example, insert the next value of the sequence ORDER_SEQ into a table ORDERS with columns ORDERNO and CUSTNO.

First, create the table ORDERS:

CREATE TABLE ORDERS (ORDERNO SMALLINT NOT NULL,
CUSTNO SMALLINT);

Then, insert the sequence value:

INSERT INTO ORDERS (ORDERNO, CUSTNO)

VALUES (NEXT VALUE FOR ORDER_SEQ, 12)

Running the following statement returns the values in the columns:

SELECT * 

FROM ORDERS

Table 1. Results for SELECT from table ORDERS

ORDERNO	CUSTNO
500	12

In this example, the next value for sequence ORDER is inserted into the ORDERNO column. Issue the INSERT statement again. Then run the SELECT statement.

Table 2. Results for SELECT from table ORDERS

ORDERNO	CUSTNO
500	12
501	12

You can also insert the previous value for the sequence ORDER by using the PREVIOUS VALUE expression. You can use NEXT VALUE and PREVIOUS VALUE in the following expressions:

• Within the select-clause of a SELECT statement or SELECT INTO statement as long as the statement does not contain a DISTINCT keyword, a GROUP BY clause, an ORDER BY clause, a UNION keyword, an INTERSECT keyword, or an EXCEPT keyword

• Within a VALUES clause of an INSERT statement

• Within the select-clause of the fullselect of an INSERT statement

• Within the SET clause of a searched or positioned UPDATE statement, though NEXT VALUE cannot be specified in the select-clause of the subselect of an expression in the SET clause

You can alter a sequence by issuing the ALTER SEQUENCE statement. Sequences can be altered in the following ways:

• Restarting the sequence

• Changing the increment between future sequence values

• Setting or eliminating the minimum or maximum values

• Changing the number of cached sequence numbers

• Changing the attribute that determines whether the sequence can cycle or not

• Changing whether sequence numbers must be generated in order of request

For example, change the increment of values of sequence ORDER from 1 to 5:

ALTER SEQUENCE ORDER_SEQ INCREMENT BY 5

After this change is complete, run the INSERT statement again and then the SELECT statement. Now the table contains the following columns.

Table 3. Results for SELECT from table ORDERS

ORDERNO	CUSTNO
500	12
501	12
528	12

Notice that the next value that the sequence uses is a 528. At first glance, this number appears to be incorrect. However, look at the events that lead up to this assignment. First, when the sequence was originally create, a cache value of 24 was assigned. The system assigns the first 24 values for this cache. Next, the sequence was altered. When the ALTER SEQUENCE statement is issued, the system drops the assigned values and starts up again with the next available value; in this case the original 24 that was cached, plus the next increment, 5. If the original CREATE SEQUENCE statement did not have the CACHE clause, the system automatically assigns a default cache value of 20. If that sequence was altered, then the next available value is 25.

• Comparison of identity columns and sequences
  While identity columns and sequences are similar in many ways, there are also differences.

 

Parent topic:

Data definition language

 

Related concepts

Sequences