Home

	Expand Search

Transact-SQL User's Guide

Chapter 11 Creating Indexes on Tables

Specifying index options

Specifying index options

The index options ignore_dup_key, ignore_dup_row, and allow_dup_row control what happens when a duplicate key or duplicate row is created with insert or update. Table 11-2 shows which option to use, based on the type of index.

Using the ignore_dup_key option

If you try to insert a duplicate value into a column that has a unique index, the command is canceled. You can avoid this situation by including the ignore_dup_key option with a unique index.

The unique index can be either clustered or nonclustered. When you begin data entry, any attempt to insert a duplicate key is canceled with an error message. After the cancellation, any transaction that was active at the time may continue as though the update or insert had never taken place. Nonduplicate keys are inserted normally.

You cannot create a unique index on a column that already includes duplicate values, whether or not ignore_dup_key is set. If you attempt to do so, Adaptive Server prints an error message and a list of the duplicate values. You must eliminate duplicates before you create a unique index on the column.

Here is an example of using the ignore_dup_key option:

create unique clustered index phone_ind
on friends_etc(phone)
with ignore_dup_key

Using the ignore_dup_row and allow_dup_row options

ignore_dup_row and allow_dup_row are options for creating a nonunique, clustered index. These options are not relevant when creating a nonunique, nonclustered index. Since an Adaptive Server nonclustered index attaches a unique row identification number internally, it never worries about duplicate rows--even for identical data values.

ignore_dup_row and allow_dup_row are mutually exclusive.

A nonunique clustered index allows duplicate keys, but does not allow duplicate rows unless you specify allow_dup_row.

If allow_dup_row is set, you can create a new nonunique, clustered index on a table that includes duplicate rows, and you can subsequently insert or update duplicate rows.

If any index in the table is unique, the requirement for uniqueness--the most stringent requirement--takes precedence over the allow_dup_row option. Thus, allow_dup_row applies only to tables with nonunique indexes. You cannot use this option if a unique clustered index exists on any column in the table.

The ignore_dup_row option eliminates duplicates from a batch of data. When you enter a duplicate row, Adaptive Server ignores that row and cancels that particular insert or update with an informational error message. After the cancellation, any transaction that may have been active at the time continues as though the insert or update had never taken place. Non-duplicate rows are inserted normally.

The ignore_dup_row applies only to tables with nonunique indexes: you cannot use this keyword if a unique index exists on any column in the table.

Table 11-3 illustrates how allow_dup_row and ignore_dup_row affect attempts to create a nonunique, clustered index on a table that includes duplicate rows, and to enter duplicate rows into a table.

Using the sorted_data option

The sorted_data option of create index speeds creation of an index when the data in the table is already in sorted order, for example, when you have used bcp to copy data that has already been sorted into an empty table. The speed increase becomes significant on large tables and increases to several times faster in tables larger than 1GB.

If sorted_data is specified but data is not in sorted order, an error message displays and the command is aborted.

This option speeds indexing only for clustered indexes or unique nonclustered indexes. Creating a nonunique nonclustered index will, however, be successful unless there are rows with duplicate keys. If there are rows with duplicate keys, an error message displays and the command is aborted.

Certain other create index options require a sort even if sorted_data is specified. See the create index description in the Reference Manual.

Using the on segment_name option

The on segment_name clause specifies a database segment name on which the index is to be created. A nonclustered index can be created on a different segment than the data pages. For example:

create index titleind 
on titles(title) 
on seg1

If you use segment_name when creating a clustered index, the table containing the index moves to the segment you specify. See a System Administrator or the Database Owner before creating tables or indexes on segments; certain segments may be reserved for performance reasons.