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.

This table shows which option to use, based on the type of index.

• ignore_dup_key Option
  If you need to insert a duplicate value, you can use ignore_dup_key option with a unique index.
• ignore_dup_row and allow_dup_row
  Use ignore_dup_row and allow_dup_row to create a nonunique, clustered index.
• sorted_data Option
  The sorted_data option of create index speeds index creation when the data in the table is already in sorted order, for example, when you have used bcp to copy sorted data into an empty table.
• on segment_name Option
  A nonclustered index can be created on a different segment than the data pages. The on segment_name clause specifies a database segment name on which the index is to be created.