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

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 int_datatype clause is silently ignored and lost when dumping the CREATE SEQUECE definition via msqldump program.

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

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

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:

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