Sequences and columns: New sequences and columns use NOORDER by default¶

Attention

This behavior change is in the 2024_01 bundle.

For the current status of the bundle, refer to Bundle History.

When you create a new sequence or a new auto-incremented column, you can specify the ORDER or NOORDER parameter to indicate whether or not the sequence can generate new values in increasing order.

ORDER specifies that the values generated for a sequence or auto-incremented column are in increasing order (or, if the interval is a negative value, in decreasing order).

For example, if a sequence or auto-incremented column has START 1 INCREMENT 2, the generated values might be 1, 3, 5, 7, 9, etc.
NOORDER specifies that the values are not guaranteed to be in increasing order.

For example, if a sequence has START 1 INCREMENT 2, the generated values might be 1, 3, 101, 5, 103, etc.

NOORDER can improve performance when multiple INSERT operations are performed concurrently (for example, when multiple clients are executing multiple INSERT statements).

If you do not specify ORDER or NOORDER, a default value is used. This default value is changing in order to improve performance:

Before the change:

If you create a new sequence without specifying ORDER or NOORDER, ORDER is used by default.
If you create a new table column and specify AUTOINCREMENT without specifying ORDER or NOORDER, ORDER is used by default.

After the change:

If you create a new sequence without specifying ORDER or NOORDER, NOORDER is used by default.
If you create a new table column and specify AUTOINCREMENT without specifying ORDER or NOORDER, NOORDER is used by default.

Note the following:

The changes to these default values do not affect existing sequences and existing auto-incremented columns.

The changes only affect new sequences and columns that are created when the behavior change is enabled.
The ORDER and NOORDER properties have no effect on the uniqueness of the generated values for sequences and auto-incremented columns.

Changing the default from NOORDER to ORDER¶

To set the default back to ORDER, set the NOORDER_SEQUENCE_AS_DEFAULT parameter to FALSE for the account, user, or session.

If you set this parameter, the value that you set overrides the value in the 2024_01 behavior change bundle. Setting this parameter to FALSE keeps the ORDER as the default, even after the 2024_01 behavior change bundle is generally enabled.

Changes to the output of the GET_DDL function¶

In addition, the output of the GET_DDL function is changing for auto-incremented columns that have START 1 INCREMENT 1 set:

Before the change:: The column definition returned by GET_DDL only includes the AUTOINCREMENT keyword (for example, column name data type AUTOINCREMENT).
After the change:: The column definition returned by GET_DDL includes all properties, including the START property, the INCREMENT property and the ORDER / NOORDER property (for example, column name data type AUTOINCREMENT START 1 INCREMENT 1 NOORDER).

Determining if a sequence or column has the ORDER or NOORDER property¶

Finally, to determine if a sequence has the ORDER or NOORDER property, you can use the following commands and views:

For sequences, you can use any of the following:
- The SHOW SEQUENCES command.
- The DESCRIBE SEQUENCE command.
- The SEQUENCES view in INFORMATION_SCHEMA.
For auto-incremented columns, you can check either of the following:
- The autoincrement column in the output of the SHOW COLUMNS command.
- The identity_ordered column in the COLUMNS view in INFORMATION_SCHEMA.
For sequences and auto-incremented columns, you can check for the ORDER or NOORDER property in the sequence or column definition returned by the GET_DDL function.

Ref: 1483