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

• Specify the HEADING and UPSHIFT attributes for your application’s use, if applicable. For
more information, see “Specifying Column Attributes” (page 94).
• Collation of numeric values occurs with negative numbers preceding positive numbers.
• For the purpose of sorting, the null value is considered to be greater than all other values.
• For compatibility of SQL/MX data types, two data types are comparable if a value of one
data type can be compared to a value of another data type. Two data types are compatible
if a value of one data type can be assigned to a column of the other data type. Compatible
data types are also comparable.
Any numeric data type can be compared with all other numeric data types in DML comparison
expressions. Character strings can be compared only if they are of the same character set.
Character strings and numeric data types are not compatible with each other and cannot be
compared directly by NonStop SQL/MX. For more information, see the SQL/MX Reference
Manual.
• You cannot use a datetime data type with SQL/MX data types other than interval in arithmetic
expressions or comparisons. Interval values can be multiplied or divided by scalar data types
and added to or subtracted from datetime data types.
• A SIGNED column is required for a number with 10 or more digits.
Performance Considerations
To achieve maximum performance when defining columns:
• Define the column data type so that the values stored in the column match the use of the data
in applications. To improve application performance, try to eliminate unnecessary data
conversion in programs.
• For the best performance, varying-length columns should be trailing. Fortunately, NonStop
SQL/MX automatically puts fixed-length columns before varying-length columns (VARCHAR,
NCHAR VARYING) in table rows, independent of what you specify. For all other data types,
the column structure within the table does not affect the performance of queries or updates.
• Define columns as numeric if they contain numeric-only values. NonStop SQL/MX calculates
its execution plan more accurately for numerically defined data.
• Do not define columns as SIGNED numeric unless they need to be signed. Signed columns
are less efficient than unsigned columns.
• Avoid specifying odd-length strings, such as CHAR (1), CHAR (3), or VARCHAR (5) for columns
that have a single byte character set (for example, ISO88591).
• Two moves are required to handle the filler required when an odd-length string precedes a
number, INTERVAL, varying-length, or nullable column.
• Define data types to match those used in host variables or by users, or encourage those who
use and program the system to match the data types in the database, including datetime data
type ranges. This strategy minimizes data type translations. For example, a NUMERIC data
type in DDL might translate to a double data type for the host variable in C code. In this
instance, you could change the DDL definition to FLOAT(52) so that the two match and do not
require translation.
Defining Character Data
NonStop SQL/MX includes both fixed-length character data and variable-length character data.
The data types for character data are:
CHARFixed-length characters
PIC
NCHAR, NATIONAL
88 Creating an SQL/MX Database