Index in sql | Drop index in sql - sql - sql tutorial - learn sql



  • Use the DROP INDEX statement to remove an index or domain index from the database.
  • When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
  • 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.
  • The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints.
  • To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.
SQL > ALTER TABLE > Drop Index Syntax
  • Sometimes we may decide that a particular index is no longer needed for a table.
  • In those cases, that index should be removed to free up storage.
  • To drop an index in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the DROP INDEX command.
  • The SQL syntax to drop an index from a table is,
ALTER TABLE "table_name"
DROP INDEX "index_name";
  • Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Wikitechy Customer

Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date datetime
  • Assume we want to drop the index created in the ALTER TABLE ADD INDEX section. To do this, we type in the following:
ALTER TABLE Wikitechy_Customer DROP INDEX IDX_COUNTRY;
  • Please note that using ALTER TABLE to drop an index in supported in MySQL but not in Oracle or SQL Server.

Example:

  • -- Syntax for SQL Server (All options except filegroup and file stream apply to Azure SQL Database.)
DROP INDEX [ IF EXISTS ]   
{ <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" } ]  
}  


  • Syntax for Azure SQL Database
DROP INDEX  
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ]   
}  

<drop_relational_or_xml_or_spatial_index> ::=   
    index_name ON <object>  

<object> ::=   
{  
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name  
}  


  • Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
DROP INDEX index_name ON [ database_name . [schema_name ] . | schema_name . ] table_name  
[;]  

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Index in sql | Drop index in sql