Database Reference
In-Depth Information
Figure 18.21
Add a Non-
nullable Column to
a Nonempty Table.
Columns can be renamed within a table.
Columns can be set as unused. Setting a column to unused marks the
column and its data as being inaccessible from SQL. The data is not
removed. This option is effective for extremely large tables. Removing
a column from a table requires extensive physical restructuring and
can be very time consuming.
Unused columns can be dropped. At some stage, unused columns
could occupy excessive physical space. The original motivation for
setting the column unused rather than simply dropping it could be
outweighed by a need for space. In addition, you can render columns
inaccessible right away and then perform the time-consuming task of
removing columns overnight or over the weekend when your users
will not be disturbed.
Note:
On a modern global scale, OLTP database downtime might be a rarity.
Opting to set columns as unused, as opposed to dropping them, avoids
data restructuring. What is restructuring of data? Every row occupies physi-
cal space on a disk. This physical space equates to a row length for each row.
This row length can be fixed or variable for each table, depending on col-
umn datatypes. Therefore, variable row lengths are assumed. Restructuring
of data as a result of column length changes or column additions in tables
