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
[;]