Serial Types

As of 2003 the SQL standard supports serial types and 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.

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

int_datatype:
     BIGINT
   | INTEGER
   | INT
   | SMALLINT
   | TINYINT
   | HUGEINT

When a serial_data_type is used in a table definition it will implicitly create a sequence, set the default clause to next value for "schemaname"."seq_####" and use it during inserts when no value is specified.
SERIAL will use an int as column data type, BIGSERIAL will use a bigint as column data type. For AUTO_INCREMENT and GENERATED ... you need to specify which integer data type you want to use explicitly.
Note that when using SERIAL or BIGSERIAL it will also implicitly generate a primary key constraint and a NOT NULL constraint for the column.

It is also possible to create and use sequences yourself.

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

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

alter_seq_params:
    [ AS seq_int_datatype ]
    [ RESTART [ WITH { bigint | subquery } ] ]
    [ INCREMENT BY bigint ]
    [ MINVALUE bigint | NO MINVALUE ]
    [ MAXVALUE bigint | NO MAXVALUE ]
    [ CACHE bigint ]
    [ [ NO ] CYCLE ]

seq_int_datatype:
     BIGINT
   | INTEGER
   | INT
   | SMALLINT
   | TINYINT

next-statement:
     NEXT VALUE FOR sequence_name

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.

Note that sequences internally always use a bigint for keeping track of the current sequence value.
Warning the AS seq_int_datatype clause is silently ignored and lost when dumping the CREATE SEQUENCE definition via msqldump or mclient program.

A sequence can only be dropped when the references (e.g. in the DEFAULT specification of a column) have previously been removed.

Associated information_schema view: information_schema.sequences

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 MySQL auto_increment type are also supported and mapped onto a sequence type in MonetDB.

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
       )
);

select id, number, name, type, type_digits, table_id, "default", "null" from sys.columns
 where table_id in (select id from sys.tables where name = 'test_serial');
+------+--------+-------+------+-------------+----------+---------------------------------+-------+
| id   | number | name  | type | type_digits | table_id | default                         | null  |
+======+========+=======+======+=============+==========+=================================+=======+
| 7907 |      0 | d     | date |           0 |     7916 | null                            | true  |
| 7908 |      1 | id    | int  |          31 |     7916 | next value for "sys"."seq_7900" | false |
| 7911 |      2 | count | int  |          31 |     7916 | next value for "sys"."seq_7901" | true  |
| 7912 |      3 | info  | int  |          31 |     7916 | next value for "sys"."seq_7902" | true  |
+------+--------+-------+------+-------------+----------+---------------------------------+-------+

select * from sys.sequences;
+------+-----------+----------+-------+----------------------+---------------------+-----------+----------+-------+
| id   | schema_id | name     | start | minvalue             | maxvalue            | increment | cacheinc | cycle |
+======+===========+==========+=======+======================+=====================+===========+==========+=======+
| 7913 |      2000 | seq_7900 |     1 |                    0 | 9223372036854775807 |         1 |        1 | false |
| 7914 |      2000 | seq_7901 |     1 |                    0 | 9223372036854775807 |         1 |        1 | false |
| 7915 |      2000 | seq_7902 |   100 | -9223372036854775807 |                1000 |         2 |        2 | true  |
+------+-----------+----------+-------+----------------------+---------------------+-----------+----------+-------+

Much like other primary database objects, the sequence object 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(schema_name, sequence_name) and sys.next_value_for(schema_name, sequence_name) 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 schema_name.sequence_name is an equivalent of sys.next_value_for(schema_name, sequence_name).
The following queries demonstrate how these functions and statement work:

sql>CREATE SEQUENCE "myseq" AS INTEGER;
operation successful
sql>SELECT get_value_for('sys', 'myseq') as seqval;
+------+
|seqval|
+======+
|    1 |
+------+
1 tuple
sql>SELECT next_value_for('sys', 'myseq') as seqval;
+------+
|seqval|
+======+
|    1 |
+------+
1 tuple
sql>SELECT NEXT VALUE FOR myseq as seqval;
+------+
|seqval|
+======+
|    2 |
+------+
1 tuple
sql>SELECT get_value_for('sys', 'myseq') as seqval;
+------+
|seqval|
+======+
|    3 |
+------+
1 tuple
sql>DROP SEQUENCE "myseq";
operation successful

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;
+--------+
| t | v  |
+========+
| 2 | ab |
+--------+
1 tuple