Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


CREATE SEQUENCE


Creates a new sequence.



Usage

Embedded/Interactive/ODBC/JDBC.

Description

A new sequence is created.

The sequence-name should follow the normal rules for naming database objects, see Naming Objects.

If sequence-name is specified in its unqualified form, the sequence will be created in the schema which has the same name as the current ident.

If sequence-name is specified in its fully qualified form (i.e. schema-name.sequence-name) the sequence will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

The construct CURRENT_VALUE OF sequence-name will return the current value of the sequence, see CURRENT_VALUE.

The construct NEXT_VALUE OF sequence-name will return the next value for the sequence and this will be established as the current value of the sequence, see NEXT_VALUE.

A sequence can be defined as unique or non-unique (the default is non-unique).

A sequence generates a series of values by starting at the initial value and proceeding in increment steps. When the addition of an increment would produce a value exceeding the specified maximum, the sequence cycles back into the beginning of the value series by the appropriate number of steps to generate the next value.

A sequence can make more than one pass between the initial value and the maximum value to generate its series of values, depending on the initial value, increment step and the maximum value.

A non-unique sequence will continue to cycle indefinitely and thus may repeatedly generate its series of values.

A unique sequence will only go through its value-generating cycle once and is guaranteed never to return the same value twice.

Restrictions

Two sequences with the same name cannot belong to the same schema.

An ident must have USAGE privilege on the sequence in order to use it.

Notes

If an initial value is not specified, a value of 1 is assumed.

If an increment is not specified, a value of 1 is assumed.

If a maximum value is not specified, a value equal to the largest INTEGER (with no precision specified) value possible in Mimer SQL is assumed (i.e. 2 147 483 647).

The sequence is created with an undefined current value initially. When NEXT_VALUE OF sequence-name is used for the first time after the sequence is created, the initial value for the sequence is returned and established as the current value of the sequence.

If CURRENT_VALUE OF sequence-name is used when the current value of the sequence is undefined, an error will be raised.

The CURRENT_VALUE OF sequence-name and NEXT_VALUE OF sequence-name constructs may be used in contexts where a value-expression would normally be used. They may also be used in the DEFAULT clause of the ALTER TABLE, CREATE DOMAIN and CREATE TABLE statements.

Examples

The following non-unique sequence:

 CREATE SEQUENCE my_seq01
 INITIAL_VALUE = 1
 INCREMENT     = 3
 MAX_VALUE     = 10;
 

will generate the following (repeating) series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7, 10, 3, 6

The following unique sequence:

 CREATE UNIQUE SEQUENCE my_seq02
 INITIAL_VALUE = 1
 INCREMENT     = 3
 MAX_VALUE     = 10;
 

will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.

The following sequence:

 CREATE UNIQUE SEQUENCE my_seq03
 INITIAL_VALUE = 1
 INCREMENT     = -1
 MAX_VALUE     = -5;
 

will generate the following series of values: 1, 0, -1, -2, -3, -4, -5.

Note: It is possible that not every value in the series of values defined by the sequence will be generated. If a database server crash etc. occurs during the life of a sequence, it is possible that some of the values in the series might be skipped.

For more information, see the Mimer SQL User's Manual, Creating Sequences.

Standard Compliance

Standard
Compliance
Comments

Mimer SQL extension
The CREATE SEQUENCE statement is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX