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.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Alter Index
Next: SQL Drop