Neoview SQL Reference Manual (R2.2)

Tables and Triggers
If you plan to create triggers on a table, its primary key length cannot exceed 2032 bytes. A table
which will not have triggers can have a primary key of 2048 bytes. For details about this limit,
see “Triggers and Primary Keys” (page 84).
Calculating Row Size
The row size required for Neoview SQL tables is dependent on the number of variable length
columns and number of columns that can be null.
The following table has four nullable columns and one variable length column. The calculation
for the size of each of the fields is listed beside the column definition. In addition to the size of
each field, there is an additional four bytes added at the beginning of each row inserted for a
maximum size of 310 bytes.
Create Table CustOrder
(orderKey largeint not null, -- 8 bytes
itemNum smallint not null, -- 2 bytes
orDate date not null, -- 4 bytes
shipDate date, -- 6 bytes
shipMode char(12), -- 14 bytes
rtnDate date, -- 6 bytes
rtnReason varchar(256), -- 266 bytes
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.
CREATE TABLE Statement 75