Page 114 - SQL

Inserting with a Unique Index

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1;

This will fail if an unique index is set on the Email column of Customers. However, alternate
behavior can be defined for this case:

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1 ON DUPLICATE KEY;

SAP ASE: Drop index

This command will drop index in the table. It works on SAP ASE server.

Syntax:

DROP INDEX [table name].[index name]

Example:

DROP INDEX Cars.index_1

Sorted Index

If you use an index that is sorted the way you would retrieve it, the SELECT statement would not do
additional sorting when in retrieval.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

When you execute the query

SELECT * FROM scoreboard ORDER BY score DESC;

The database system would not do additional sorting, since it can do an index-lookup in that order.

Dropping an Index, or Disabling and Rebuilding it

DROP INDEX ix_cars_employee_id ON Cars;

We can use command DROP to delete our index. In this example we will DROP the index called
ix_cars_employee_id on the table Cars.

This deletes the index entirely, and if the index is clustered, will remove any clustering. It cannot
be rebuilt without recreating the index, which can be slow and computationally expensive. As an
alternative, the index can be disabled:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE;

https://riptutorial.com/ 96