SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-20
Defining Columns
When determining the data type and attributes for a column, consider these guidelines:
•
SQL/MP supports the ASCII character set and several other character sets for
character data. For more information, see Defining Character Data on page 5-21.
•
Specify a column default value for each column. This default value must be
DEFAULT, DEFAULT SYSTEM, NOT NULL, NULL, NO DEFAULT, DEFAULT
NULL, or LITERAL literal, or a valid combination of these values. For more
information, see Using Default and Null Values on page 5-26.
•
Specify any of these attributes for your application’s use, if applicable: HEADING,
HELP TEXT, and UPSHIFT. For more information, see Specifying Column
Attributes on page 5-28.
•
Collation of single-byte character data is performed in the order represented by the
ordinal positions of the characters in the ASCII set. Alternatively, you can specify a
different collating sequence for single-byte character data by creating a collation
object and associating the collation with the character data. If you do associate a
collation with the column, the character set associated with the collation must be
the same as the character set defined for the column.
•
Collation of numeric values occurs with negative numbers preceding positive
numbers.
•
For sorting, the null value is considered to be greater than all other values.
•
For compatibility of SQL/MP data types, any character string type can be
compared with all other character data types, and any numeric data type can be
compared with all other numeric data types in DML comparison expressions.
Character strings and numeric data types, however, are not compatible with each
other; they cannot be compared directly by SQL/MP during a retrieval using
predicates.
Comparisons between character strings and numeric data types can occur only
within user-written application code or, for parameters, by using the CAST function.
For more information about the CAST function, see the SQL/MP Reference
Manual or online help available through SQLCI.
•
A date-time data type cannot be used with other SQL/MP data types except
INTERVAL in arithmetic expressions or comparisons. INTERVAL values can be
multiplied or divided by scalar data types and added to or subtracted from date-
time data types.
•
A SIGNED column is required for a number with 10 or more digits.
Performance Considerations
To achieve maximum performance, consider these issues when defining columns:
•
Define the column data type so that the values stored in the column match the use
of the data in applications. You should attempt to eliminate unnecessary data