Index-type options

Use the DISTINCT or UNIQUE and CLUSTER options of the CREATE INDEX statement to specify the characteristics of the index.

Index-Type Options

[ { DISTINCT | UNIQUE } ]

[ CLUSTER ]

DISTINCT: Specifies that the columns on which the index is based accept only unique data.
UNIQUE: Specifies that the columns on which the index is based accept only unique data.
CLUSTER: Reorders the rows of the table in the order that the index designates.

UNIQUE or DISTINCT option usage

If you do not specify the UNIQUE or DISTINCT keyword, the index allows duplicate values in the indexed column or in the set of indexed columns.

A column with a unique index can have, at most, one NULL value.

You cannot specify an R-tree secondary-access method for a UNIQUE index key.

The following example creates a unique index that prevents duplicate values in the customer_num column:

CREATE UNIQUE INDEX c_num_ix ON customer (customer_num);

The DISTINCT and UNIQUE keywords are synonyms, so the following statement has the same effect as the previous example:

CREATE DISTINCT INDEX c_num_ix ON customer (customer_num);

The index in both examples is maintained in ascending order, which is the default order. The next example defines a unique descending index called c_num_desc_ix on the same column:

CREATE UNIQUE INDEX c_num_desc_ix ON customer (customer_num DESC);

You can also prevent duplicate values in a column or in a set of columns by creating a unique constraint with the CREATE TABLE or ALTER TABLE statement and the ADD CONSTRAINT clause.

In an NLSCASE INSENSITIVE database, indexes on columns of the NCHAR and NVARCHAR data types disregard lettercase differences, so that the database server treats case variants among strings composed of the same sequence of letters as duplicate values. You cannot insert or update a row of table with an NCHAR or NVARCHAR column on which a unique index or a unique constraint is defined, if that column value in the new row differs only by letter case from the value in the same column of any existing row of the same table. For more information about databases with the NLSCASE INSENSITIVE property, see Duplicate rows in NLSCASE INSENSITIVE databases and NCHAR and NVARCHAR expressions in case-insensitive databases.

CLUSTER option usage

You cannot specify the CLUSTER option and the ONLINE keyword in the same statement. In addition, some secondary-access methods (such as R-tree) do not support clustering. Before you specify CLUSTER for your index, be sure that the index uses an access method that supports clustering.

The CREATE CLUSTER INDEX statement fails if a CLUSTER index already exists on the same table.

CREATE CLUSTER INDEX c_clust_ix ON customer (zipcode);

This statement creates an index on the customer table and physically orders the rows according to their postal code values, in (by default) ascending order.

If the CLUSTER option is specified and fragments exist on the data, values are clustered only within each fragment, and not globally across the entire table.

If the CREATE CLUSTER INDEX statement also includes the COMPRESSED keyword as a storage option, the database server issues error -26950. To create a cluster index that supports compression requires two steps:

Use the CREATE CLUSTER INDEX statement to define a cluster index with no index compression.
Call the SQL administration API task( ) or admin( ) function with the 'index compress' argument to compress the existing cluster index.

You cannot use the CLUSTER option on a forest of trees index.