Index-type options

1 DISTINCT
1 UNIQUE
2?  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.

CREATE UNIQUE INDEX c_num_ix ON customer (customer_num); 

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.

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.

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