NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-37
Considerations—ALTER TABLE
°
ALTER TABLE ADD COLUMN automatically requests an exclusive table lock
on the table. If data in the table is already locked, ALTER TABLE waits until
the request is granted or a timeout occurs.
°
A new column appears as the last column of the table. In existing rows of the
table, the new column takes on its default value unless it has a date-time data
type with the default set to CURRENT or SYSTEM.
If you add a column with a date-time data type to a table that contains existing
rows and you specify DEFAULT CURRENT or DEFAULT SYSTEM, SQL
uses January 1, 1 A.D. 12:00:00.000000 as the default date and time for the
existing rows.
For example, an existing row receives the value 0001-01-01:12:00:00.000000 in
the new column if the data type is DATETIME YEAR TO FRACTION, receives
the value 0001-01-01 in the new column if the data type is DATE, receives the
value 12:00:00 in the new column if the data type is TIME, and so forth.
Any row added after the ADD COLUMN operation finishes that does not
contain a value for the column receives a default value based on the current
timestamp at the time the row is added.
°
The sum of the lengths of all columns for a table cannot exceed the maximum
row length for the table (the block size minus the header size). See Limits
on
page L-5 for additional restrictions on the number of columns allowed.
°
You cannot add a column to a table with relative file organization unless the row
length of the table is large enough to accommodate the added column. You
cannot add a column to a table with entry-sequenced file organization under any
circumstances.
°
The new column is not actually added to a row until the row is updated. If you
select a row that does not yet have the new column, SQL returns the default
value for the column.
•
Reconfiguring partitions of tables
MOVE can perform a simple move operation for a table of any file organization,
and can perform a one-way or two-way split (to new partitions), merge (into an
existing partition), or one-way move (to an existing partition) for a table with a key-
sequenced file organization.
°
A simple move moves a partition to another volume:
MOVE TO dest-part [WITH SHARED ACCESS]
name specifies the name of the partition to be moved. You can specify a simple
move with or without the WITH SHARED ACCESS option.
°
A merge operation moves the partition into another existing partition, deleting
the original partition:
MOVE TO dest-part WITH SHARED ACCESS
In a merge request, name specifies the actual partition being moved. The WITH
SHARED ACCESS option is required.