SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

◦ Use fillers as required to attain proper alignment:
Recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL, c
is SMALLINT NOT NULL, and b is INT NOT NULL.
–
– Not recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL,
b is INT NOT NULL, and c is SMALLINT NOT NULL.
◦ Unaligned fields cannot choose bulk move.
• Avoid using signed numeric data types. Signed numbers are expensive to process and
expensive to encode for key access. Unsigned numbers do not need encoding.
• Avoid using DECIMAL data types when scale and precision are not needed. For example,
use INT instead of NUMERIC(8,0).
• Use numbers instead of characters whenever possible. For example, an INT field is better
than a CHAR(8) field for numeric employee IDs.
• Use VARCHARs carefully:
Length processing of VARCHARS is expensive.◦
◦ Use fixed character data types if maximum data length is “small.”
◦ Use VARCHARS if the variance of data size is large.
• Do not put character fields between noncharacter fields.
The C/C++ preprocessor generates an extra null terminator.◦
◦ Bulk move degradation results.
• Use datetime fields carefully:
Datetime operations are expensive.◦
◦ Use datetime fields if datetime arithmetic is needed in an SQL query (for example, where
date-col + interval '1' day > date '2001-09- 11').
◦ Use LARGEINT or CHAR if datetime is needed for storage and retrieval only.
• Where possible, avoid adding columns to a table:
Added columns turn off internal expression optimizations.◦
◦ If you add a column, reload the table.
Accessing an SQL/MP Database From NonStop SQL/MX
From NonStop SQL/MX, you can run queries on an SQL/MP database located on the same node.
Such queries must conform to these guidelines:
• Perform DML operations only. From NonStop SQL/MX, you cannot perform DDL operations
or utility operations on SQL/MP database objects. If you want to perform DDL operations and
utility operations on SQL/MP database objects, you must do so from NonStop SQL/MP.
• Use either valid SQL/MP aliases or Guardian names for SQL/MP objects.
• Make sure that all SQL/MP objects comply with the Guardian security conventions as described
in the SQL/MP Installation and Management Guide.
102 Creating an SQL/MX Database