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

Sample table: agents

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