Serial Types

As of 2003 the SQL standard supports serial types (sequences). They are of particular use in auto-generating key values. A serial type is defined as a primary database object over any of the built-in data types. The NEXT VALUE FOR operation generates the next value and can be used anywhere a value expression is allowed. It's name should be unique within the current schema. A sequence can only be dropped when the references (e.g. in the DEFAULT specification of a column) have previously been removed.

sequence_commands:
     CREATE SEQUENCE [ schema_name '.'] seq_name [ seq_params ]
   | DROP SEQUENCE [ schema_name '.'] seq_name
   | ALTER SEQUENCE [ schema_name '.'] seq_name [ alter_seq_params ]

serial_data_types:
     SERIAL
   | BIGSERIAL
   | int_datatype AUTO_INCREMENT
   | int_datatype GENERATED ALWAYS AS IDENTITY [ '(' seq_params ')' ]

int_datatype:
    BIGINT | INTEGER | INT | SMALLINT | TINYINT

seq_params:
    [ AS int_datatype ]
    [ START WITH bigint ]
    [ INCREMENT BY bigint ]
    [ MINVALUE bigint | NO MINVALUE ]
    [ MAXVALUE bigint | NO MAXVALUE ]
    [ CACHE bigint ]
    [ [ NO ] CYCLE ]

alter_seq_params:
    [ AS int_datatype ]
    [ RESTART [WITH bigint ] ]
    [ INCREMENT BY bigint ]
    [ MINVALUE bigint | NO MINVALUE ]
    [ MAXVALUE bigint | NO MAXVALUE ]
    [ CACHE bigint ]
    [ [ NO ] CYCLE ]
next-statement:
 NEXT VALUE FOR sequence_name

Associated system table: sys.sequences

Example.

The example shown below introduces the column count, which is incremented with each row being added. It is conceptually identical to the value expression max(count)+1 in each insert. The column info is a limited range with wrap around.The serial type as found in PostgreSQL and the auto_increment
flag as found in MySQL are both mapped onto a sequence type in MonetDB/SQL.

CREATE TABLE test_serial (  
   d DATE,  
   id SERIAL,  -- this will implicitly create a PKey. Use BIGSERIAL if you want the id to be of type bigint instead of int.  
   count INT AUTO_INCREMENT,  
   info INT GENERATED ALWAYS AS
        IDENTITY (  
           START WITH 100 INCREMENT BY 2  
           NO MINVALUE MAXVALUE 1000  
           CACHE 2 CYCLE  
)      );

Much like other primary database objects, the sequence type can be altered at any time as illustrated below.

sql>CREATE SEQUENCE "my_test_seq" as integer START WITH 2;  
sql>CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v char);  
sql>INSERT INTO test(v) VALUES ('a');  
Rows affected 1  
sql>INSERT INTO test VALUES (10, 'b');  
Rows affected 1  
sql>ALTER SEQUENCE "my_test_seq" RESTART WITH (SELECT MAX(t) + 1 FROM test);  
sql>INSERT INTO test(v) VALUES ('c');  
Rows affected 1  
sql>SELECT * FROM test;  
+----+---+  
| t  | v |  
+====+===+  
| 2  | a |  
| 10 | b |  
| 11 | c |  
+----+---+

The functions sys.get_value_for('', '') and sys.next_value_for('', '') can be used to query the current value of a sequence. The difference is that next_value_for() also advances the current value of a sequence to the next value.
The SQL statement SELECT NEXT VALUE FOR . is an equivalent of sys.next_value_for('', ''). The following queries demonstrate how these functions and statement work:

operation successful  
sql>SELECT get_value_for('sys', 'myseq');  
+------+  
| L2   |  
+======+  
|    1 |  
+------+  
1 tuple  
sql>SELECT next_value_for('sys', 'myseq');  
+------+  
| L2   |  
+======+  
|    1 |  
+------+  
1 tuple  
sql>SELECT NEXT VALUE FOR myseq;  
+------+  
| L2   |  
+======+  
|    2 |  
+------+  
1 tuple  
sql>SELECT get_value_for('sys', 'myseq');  
+------+  
| L2   |  
+======+  
|    2 |  
+------+  
1 tuple

CREATE SEQUENCE "my_test_seq" as integer START WITH 2;
CREATE TABLE test (t int DEFAULT NEXT VALUE FOR "my_test_seq", v varchar(10));
INSERT INTO test(v) VALUES ('ab');
SELECT * FROM test;