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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-24
Managing Table Data
•
Specify the HEADING and UPSHIFT attributes for your application’s use, if
applicable. For more information, see Specifying Column Attributes on page 7-32.
•
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.