Using Sequences in database systems

A Sequence is a database object capable to generate numbers (i.e. it is a number generator). One of the main uses of Sequences, is to create sequential numbers used for ID columns.

Sequences are available in several DBMS, including Oracle, MS SQL Server, PostgreSQL and MariaDB.

Creating and dropping sequences

In all these DBMS, sequences are created using a Create Sequence command and drop with a Drop Sequence.

A basic command to create a sequence is:

CREATE SEQUENCE seqBasicExample;

Some of the most common parameters you may specify when creating a sequence are:

•  INCREMENT number – specifies the increment in the sequence value when a user queries for next value;
•  MINVALUE minvalue  – the lowest allowed value for the sequence;
•  MAXVALUE number – the highest allowed value for the sequence;
•  START [ WITH ] start ] – the start value for the sequence;
•  CYCLE – specify that the sequence value must restart when a user queries for the next value and such value is above the highest allowed one (i.e. MAXVALUE);
• CACHE number – especifies if the DBMS should cache the following available sequence’s values in main memory. Using cache would speedup the use of sequences, but if the DBMS aborts or is restarted, the values in main memory are lost.

Now, let’s see an example of a create sequence command that uses such parameters:

mydb=# Create sequence seqCycleSample start with 10 increment 5 minvalue 1 maxvalue 10000 cycle cache 10;
CREATE SEQUENCE
mydb=#

The above SQL command creates the sequence seqCycleSample. The first value of this sequence is 10. Every time a user queries for a new value, the sequence’s current value is incremented by 5. After the highest allowed value (10000), the next value is 1 (as cycle is specified and minvalue is 1). The system maintains the following 10 values of the sequence in cache.

If you want to drop a sequence, then just use drop sequence, as follows:

mydb=# drop sequence seqBasicExample;
DROP SEQUENCE
mydb=#

Query for the value of a sequence

Once you have created a sequence, you may create for its next value and, then, use it. The detailed syntax for querying for a sequence’s next value is DBMS dependent, but most use some function, pseudo-column or key word similar to ‘nextval’. In the following code, we show how to query for the next value of the seqBasicExample sequence, we created earlier.

To query for the sequence’s next value in PostgreSQL:

mydb=# Select nextval('seqBasicExample');
 nextval
---------
       1
(1 row)

mydb=#

In the following, the same query using Oracle syntax:

Select seqBasicExample.nextval from dual;

Now, in MariaDB:

Select NEXTVAL(seqBasicExample);

And in Microsoft SQL Server:

Select NEXT VALUE for mySchema.seqBasicExample; 

Using a sequence in an Insert command

But in real would applications, you would probably use sequences in Insert commands, without having query for the sequence’s next value in a Select command. To do so, you only need to use the DBMS’s nextval variant in the insert command, as in the following examples (in which we present how to use a sequence to insert rows into the Employees table).

In PostgreSQL:

mydb=#
mydb=# Create table Employees
mydb-# (id int primary key,
mydb(# name varchar(50));
CREATE TABLE
mydb=#
mydb=# CREATE SEQUENCE seqEmployeeID;
CREATE SEQUENCE
mydb=#
mydb=# insert into Employees (id, name) values (nextval('seqEmployeeID'), 'John');
INSERT 0 1
mydb=# insert into Employees (id, name) values (nextval('seqEmployeeID'), 'Ann');
INSERT 0 1
mydb=#
mydb=# select * from Employees;
 id | name
----+------
  1 | John
  2 | Ann
(2 rows)


mydb=#

Now in Oracle, use the nextval pseudocolumn like this:

insert into Employees (id, name) values (seqEmployeeID.nextval, 'Ann');

And in Microsoft SQL Server, you may use a command like this:

insert into Employees (id, name) values (NEXT VALUE FOR seqEmployeeID, 'Ann');

Quering for the current value of a sequence

So, after you have initialized the sequence (i.e. called for its next value at least once), you may call for its current value. Again, this is DBMS specific syntax, but most DBMS use Currval or Lastval:

To query for the current value of the seqCycleSample in PostgreSQL, we may use:

mydb=# Select currval('seqCycleSample');
 currval
---------
      10
(1 row)

In Oracle, the command should be:

Select seqCycleSample.currval from dual;

In MariaDB, we should use the function LASTVAL:

Select LASTVAL(seqCycleSample);

Quering for the current value of a sequence is especially usefull when you used the sequence in a Insert command and you must use the last generated Id in another insert. For instance, consider we must register an employee and one of its dependents (using the employee’s ID). We may use SQL commands like these (Oracle syntax):

insert into Employees (id, name) values (seqEmployeeID.nextval, 'Mike');
insert into Dependents (EmpId, DepName) values (seqEmployeeID.currval, 'Peter');

Sequences and gaps

Using sequences make it really easy to generate sequential numbers. It also provides good performance, especially if the sequence is created with the cache clause. But using a sequence to generate the values for a table’s column does not provide any guarantee that such column won’t have gaps. In fact, there are some situations where sequence values are “lost”.

For instance, rollback command does not affect sequence values. So, a SQL block calls for a sequence’s next value, insert a row into a table considering such sequence, and, then, a rollback command is issued, the insert command is rolledback (i.e. the row is removed from the database), but the sequence value is not rolledback.

Note that, in the above example, the sequence value remains 3 even after the rollback command. So, in the next Insert, the value 4 would be created and the table would have no Employee with the Id 3.

There are also other situations that may lead to “lost” values. For instance, if one activates a cache for a sequence and the DBMS instance is restarted, all the cached values are lost. Hence, if having gaps in a table column is a problem for you, you should look after them even if you use sequences.

There are several ways to find if there are gaps in your columns. See some of them you our post SQL: 3 ways to find gaps and missing values .

Some links to DBMS documentation

We presented some variations in sequence related commands. But you may also check DBMS specific documentation at the following links:

• MariaDB
• Oracle
• Microsoft SQL Server
• PostgreSQL

Hope this is helpfull! Cheers!