Discovering SQL: A Hands-On Guide for Beginners

SEQUENCES

Unlike identity columns, sequences are independent RDBMS objects, not tied to a specific table. They have been implemented in Oracle, IBM DB2, PostgreSQL, and HSQLDB/OpenOffice BASE. More importantly, they have made it into the SQL Standard Committee.

The basic syntax for creating a sequence is identical across those RDBMSs supporting sequences (some might insist on adding the SCHEMA name as the first part of the sequence name). To create a generator that begins with 1, and generates sequential values in increments of 1, all you have to do is to issue this statement:

CREATE SEQUENCE seq_books;

Of course, there are quite a few optional clauses that can go with this statement: You can specify initial value, increment value, max and minimum values, the order (descending or ascending), and so on. Once a sequence is created, it can be referenced from an SQL statement by name, and its properties provide access to the current, next, and previous values.

Here are some examples of how the sequence created in the previous example can be used:

SELECT seq_books.NEXTVAL as next_value 
FROM dual;

next_value
-----------------
1

SELECT seq_books.CURRVAL as current_value 
FROM dual;

current_value
-----------------
1

SELECT seq_books.NEXTVAL as next_value 
FROM dual;

next_value
-----------------
2

The DUAL table is provided by Oracle to SELECT from when you really do not need any data. IDB DB2 is even more forthright; it wants you to select from SYSIBM.SYSDUMMY1 so you'll never forget ...