Saving disk space by using non-Padded indexes

You can save disk space by using non-padded indexes instead of padded indexes.

About this task

Introductory concepts

Indexes that are padded or not padded

When you define an index as NOT PADDED, the varying-length columns in the index are not padded to their maximum length. If the index contains at least one varying-length column, the length information is stored with the key. Consequently, the amount of savings depends on the number of varying-length columns in the index and the actual length of the columns in those indexes versus their maximum lengths.

Procedure

To use index padding efficiently:

As a general rule, use non-padded indexes only if the average amount that is saved is greater than about 18 bytes per column. For example, assume that you have an index key that is defined on a VARCHAR(128) column and the actual length of the key is 8 bytes. An index that is defined as NOT PADDED would require approximately 9 times less storage than an index that is defined as PADDED, as shown by the following calculation:

(128 + 4) / (8 + 2 + 4) = 9