Neoview SQL Reference Manual (R2.3)

primary key(orderKey, itemNum)
;
To calculate this for your specific table, use this formula:
(num of variable fields * 8) + (num of nullable fields * 2) + (num of ‘not null droppable’ fields *
2) + 4 + (size of all fields based on type)
Columns declared as ‘not null’ have no additional size other then the size of the data type declared.
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.
If the table is partitioned then the table is automatically partitioned across all the disk volumes
on the system.
Record Format
The keyword FORMAT is a non-reserved word.
If the Row Format phrase appears more than once within the ATTRIBUTES clause, Neoview
SQL issues an error.
The Row Format phrase can appear in any order within the ATTRIBUTES clause.
The Row Format phrase cannot appear within an ALTER statement.
Generating Unique Values For a 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.
RULES
• Only one IDENTITY column can be used in a table.
• Values for the IDENTITY column are generated by default. If you specify a value for the
IDENTITY column, the system uses that value and does not generate a unique value for
that row.
• The IDENTITY column must have a NOT NULL constraint. If not specified, the system will
implicitly add the constraint.
• The IDENTITY column property is retained on the target table for CREATE TABLE LIKE...
statements.
CONSIDERATIONS
• IDENTITY columns can be the PRIMARY KEY or can be part of a compound clustering key.
You must have a unique index on the IDENTITY column. It is recommended that you assign
the IDENTITY column as the clustering key to avoid an extra index on the table. This avoids
index maintenance.
• The IDENTITY column can be the partitioning key or can be part of a compound partitioning
key.
• You can specify a SIGNED LARGEINT value for an IDENTITY column. The system generates
only positive values greater than 1023.
RESTRICTIONS
76 SQL Statements