SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-21
Defining Columns
conversion in programs. Data conversions in programs can decrease application
performance.
•
Define varying-length columns (VARCHAR, NCHAR VARYING) as the last
columns of the table. For all the other data types, the column structure within the
table does not affect the performance of queries or updates. For the most efficient
use of varying-length columns, however, these columns should be trailing.
•
Define columns as numeric if they contain numeric-only values. SQL 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.
•
Place all varying-length variables at the end of a row. If a VARCHAR variable is
inside a row, the VARCHAR column is extended to its maximum length, and a
second move is required to retrieve any data after the VARCHAR.
•
Avoid specifying odd-length strings, such as CHAR (1), CHAR (3), or
VARCHAR (5).
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 date-time 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(54) so that the two match and do not require
translation.
Defining Character Data
SQL includes both fixed-length character data and variable-length character data. The
data types for character data are:
Either type of character data can be associated with a character set by specifying the
CHARACTER SET clause on the host variable declaration. A character data type is
compatible with another character data type with the same character set, but is not
compatible with numeric, date-time, or interval data types, and not with character data
associated with a different character set.
You can specify one of these character sets for a column:
CHARACTER
NCHAR
PIC X DISPLAY
Fixed-length characters
VARCHAR
CHAR VARYING
NCHAR VARYING
Variable-length characters