SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting an SQL/MP Database to SQL/MX Tables
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
11-10
Tables
Column Data Types
SQL/MX tables support most of the data types that exist in SQL/MP tables, with a few
exceptions.
For more information about SQL/MX data types, see the
SQL/MX Reference Manual.
Primary Keys
In both NonStop SQL/MP and NonStop SQL/MX, primary keys specify a column or
group of columns whose values uniquely identify the rows in a table. In NonStop
SQL/MP, the primary key defines the way that data is organized in the underlying
Guardian files. In NonStop SQL/MX, the primary key is a constraint defined by the
ANSI SQL standard. However, in NonStop SQL/MX, you can define the primary key as
NOT DROPPABLE, which is the default, so that the primary key works the same as in
NonStop SQL/MP. For more information about SQL/MX primary keys, see the
SQL/MX
Reference Manual.
Clustering Keys
Unlike NonStop SQL/MP, the SQL/MX CREATE TABLE statement does not have a
CLUSTERING KEY clause. In NonStop SQL/MX, use the STORE BY clause of the
CREATE TABLE statement to assign a clustering key for an SQL/MX table. If you
specify STORE BY PRIMARY KEY, NonStop SQL/MX bases the clustering key on the
primary key. The STORE BY clause determines the order of rows in the physical file
and affects how you can partition the file. For more information about SQL/MX
clustering keys, see the
SQL/MX Reference Manual.
Character String Columns Unlike SQL/MP tables, SQL/MX tables support only the
UCS2 or ISO88591 character set for character string
columns. NonStop SQL/MX does not support non-default
collations on SQL/MX character columns.
Date-Time Columns Many SQL/MP DATETIME data types map to SQL/MX
DATE, TIME, and TIMESTAMP data types. However,
NonStop SQL/MX does not support nonstandard date-time
data types with a start field of FRACTION. For more
information, see Table 10-9
on page 10-37.
Interval Columns Many SQL/MP interval data types map to SQL/MX interval
data types. However, NonStop SQL/MX does not support
interval data types with a start field of FRACTION. For
more information, see Table 10-10
on page 10-39.
Floating-Point Columns SQL/MX tables support floating-point columns that are in
IEEE floating-point format, not Tandem floating-point
format, which exists in SQL/MP tables.