Neoview SQL Reference Manual (R2.4 SP2)

• Number of nullable columns.
• Padding bytes for proper alignment.
• Row header.
A set of 32 nullable columns require an additional 4 bytes. Each variable length column requires
an additional 4 bytes. Though columns are rearranged to reduce the number of padding bytes,
up to 9 additional bytes might be required to ensure proper column alignment for performance
reasons.
A rough estimate can be calculated using the following formula. The actual size of the row may
vary.
Row Size in bytes = Row Header (4) +
((Number of nullable columns / 32 + 1) * 4) +
(Number of variable columns * 4) +
Sum of sizes of all columns based on type.
Creating Partitions Automatically
When creating a table, users can specify that the table is not partitioned using the NO PARTITION
clause. The default for the table is to be partitioned.
You may also specify the MAX TABLE SIZE clause that is used to allocate space on the disk. It
is advisable that you specify a value, even if it is approximate, because it helps to allocate the
appropriate amount of space for the table. If this clause is not specified, Neoview SQL will decide
how to allocate the space for the table.
If the table is partitioned then the table is automatically partitioned across all the disk volumes
if the system has less than or equal to 256 disks. If the system has more than 256 disks, then the
table is partitioned across half the disks on the system.
Generating Values For an IDENTITY Column
You can use IDENTITY columns to automatically generate unique values. The values are unique
across all partitions of the table for the IDENTITY column. IDENTITY columns are declared in
the CREATE TABLE statement. IDENTITY columns can be used as surrogate keys. They can
also be used to uniquely identify records with the same key.
Difference Between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS
IDENTITY
GENERATED BY DEFAULT AS IDENTITY allows both user-specified and system-generated
column values for the IDENTITY column. The GENERATED ALWAYS AS IDENTITY option
provides system-generated unique values. It does not allow user-specified IDENTITY column
values.
Generating the System-Generated Value For an IDENTITY Column
Neoview SQL generates the next value for an IDENTITY column in these ways:
• An INSERT statement where DEFAULT is specified as a value for the IDENTITY column.
For example: INSERT INTO tbl1 (DEFAULT, 10); assuming the first column is defined as
an IDENTITY column.
• An INSERT statement specifying the columns to be inserted, but leaving out the IDENTITY
column. For example: INSERT INTO tbl1(b) values (10); assuming that tbl1 has an IDENTITY
column and the IDENTITY column has been omitted from the column list.
Generating Unique Values For an IDENTITY Column
The sequence generator feature provides a method for generating unique values for an IDENTITY
column. Neoview SQL guarantees to generate unique values if the IDENTITY column is defined
as GENERATED ALWAYS AS IDENTITY with the NO CYCLE option.
CREATE TABLE Statement 87