SQL DROP INDEX

Drop Index

The SQL DROP INDEX drops an existing index from the database system. To execute this command you must be the owner of the index.

Syntax:

DROP INDEX <index name>;

Parameters:

Example: SQL DROP INDEX

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

The statement bellow will create an INDEX TABLE 'test_index':

CREATE INDEX test_index
ON agents (agent_code ASC);

If we want to remove the index table 'test_index' of the base table 'agents', the following SQL statement can be used :

DROP INDEX test_index;

Drop Index in MySQL, PostgreSQL, Oracle, SQL Server

Drop Index in MySQL [5.7]

In MySQL, DROP INDEX drops the index named from the table. This statement is mapped to an ALTER TABLE statement to drop the index.

Syntax:

DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Drop Index in PostgreSQL 9.3.13

DROP INDEX -- remove an index

Syntax:

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Drop Index in Oracle 11g

In Oracle, DROP INDEX statement is used to remove an index or domain index from the database.

Syntax:

DROP INDEX [ schema. ] index [ FORCE ]

Drop Index in SQL Server 2014

In SQL Server DROP INDEX Removes one or more relational, spatial, filtered, or XML indexes from the current database. You can drop a clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option.

Syntax:

-- SQL Server Syntax
DROP INDEX
  { <drop_relational_or_xml_or_spatial_index> [ ,...n ] 
  | <drop_backward_compatible_index> [ ,...n ]
  }
<drop_relational_or_xml_or_spatial_index> ::=
  index_name ON <object> 
  [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]
<drop_backward_compatible_index> ::=
  [ owner_name. ] table_or_view_name.index_name 
<object> ::=
  {
  [ database_name. [ schema_name ] . | schema_name. ] 
  table_or_view_name
  }
<drop_clustered_index_option> ::=
  {
  MAXDOP = max_degree_of_parallelism
  | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
  | filegroup_name
  | "default" 
  }
  [ FILESTREAM_ON { partition_scheme_name 
  | filestream_filegroup_name 
  | "default" }]
  }

See Also : CREATE INDEX for information on creating an index.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Alter Index
Next: SQL Drop