Altering how varying-length index columns are stored

You can use the ALTER INDEX statement to change how varying-length column values are stored in an index.

To alter how varying-length column values are stored in an index, complete the following steps:

1. Choose the padding attribute for the columns.
2. Issue the ALTER INDEX SQL statement.
   • Specify the NOT PADDED clause if you do not want column values to be padded to their maximum length. This clause specifies that VARCHAR and VARGRAPHIC columns of an existing index are stored as varying-length columns.
   • Specify the PADDED clause if you want column values to be padded to the maximum lengths of the columns. This clause specifies that VARCHAR and VARGRAPHIC columns of an existing index are stored as fixed-length columns.
3. Commit the alter procedure.

Results

The ALTER INDEX statement is successful only if the index has at least one varying-length column.

What to do next

When you alter the padding attribute of an index, the index is placed into a restricted REBUILD-pending (RBDP) state. When you alter the padding attribute of a nonpartitioned secondary index (NPSI), the index is placed into a page set REBUILD-pending (PSRBD) state. In both cases, the indexes cannot be accessed until they are rebuilt from the data.