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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×