Index modes
Use the index mode options of the CREATE INDEX statement to specify the behavior of the index during INSERT, DELETE, MERGE, and UPDATE operations.
- DISABLED
- The database server does not update the index after insert, delete, and update operations that modify the base table. The optimizer does not use the index during the execution of queries.
- ENABLED
- The database server updates the index after insert, delete, and update operations that modify the base table. The optimizer uses the index during query execution. If an insert or update operation causes a duplicate key value to be added to a unique index, the statement fails.
- FILTERING
- The database server updates a unique index after insert, delete,
and update operations that modify the base table. (This option is
not available with duplicate indexes.)
The optimizer uses the index during query execution. If an insert or update operation causes a duplicate key value to be added to a unique index in filtering mode, the statement continues processing, but the bad row is written to the violations table associated with the base table. Diagnostic information about the unique-index violation is written to the diagnostics table associated with the base table.
If you specify filtering for a unique index, you can also specify one of the following error options.
- WITHOUT ERROR
- A unique-index violation during an insert or update operation returns no integrity-violation error to the user.
- WITH ERROR
- Any unique-index violation during an insert or update operation returns an integrity-violation error to the user.
For information on changing the database object mode of a unique index, see Modes for constraints and unique indexes.