Simple arrays and associative arrays differ in multiple ways. Understanding the differences can help you to choose the right data type to use.
Arrays | Associative arrays |
---|---|
The maximum cardinality of a simple array is defined when the simple array is defined. When a value is assigned to index N, the elements with indices between the current cardinality of the array and N are implicitly initialized to NULL. | There is no user-specified maximum cardinality and no elements are initialized when an associative array variable is declared. The maximum cardinality is limited by the available free memory. |
The index data type for a simple array must be an integer value. | The index type for an associative array can be one of a set of supported data types. |
The index values in a simple array must be a contiguous set of integer values. | In an associative array the index values can be sparse. |
The CREATE TYPE statement for a simple array
does not require the specification of the array cardinality. For example,
in this statement, no cardinality is specified:
|
In the CREATE TYPE statement for an associative
array, instead of requiring a specification of the array cardinality,
the index data type is required. For example, in this statement,
the cardinality for the index data type is specified as INTEGER:
|
A first assignment to a simple array results
in the initialization of array elements with index values between
1 and the index value assigned to the array. The following compound
SQL (compiled) statement contains the declaration of a simple array
variable and the assignment of a value to the variable:
After the execution of the assignment statement, the cardinality
of mySimpleA is 100; the elements with indices with
values 1 to 99 are implicitly initialized to NULL. |
A first assignment to an associative array results
in the initialization of a single element with a single index value.
The following compound SQL (compiled) statement contains the declaration
of an associative array variable and the assignment of a value to
the variable:
After the execution of the assignment statement,
the cardinality of the array is 1. |