SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-22
Managing Table Data
•
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.