DROP INDEX

Remove one or more relational or XML indexes from the current database.

Syntax
      DROP INDEX index ON object [WITH ( options [ ,...n ] ) ]
  
      DROP INDEX [owner.] table_or_view.index
             This syntax for backwards compatibility only (pre 2005)

Options:
    MAXDOP = max_degree_of_parallelism
    ONLINE = { ON | OFF }
    MOVE TO { partition_scheme_name (column) 
    filegroup_name
    "default" 

object:
    database.schema.table_or_view
    database..table_or_view
    schema.table_or_view

Setting MAXDOP/Max_degree_of_parallelism will override the max degree of parallelism configuration option. (Max = 64 processors)

The MOVE TO option (in SQL Server 2005) allows you to drop a clustered index and move the resulting table to another filegroup or partition scheme in a single transaction.

To drop an index created as part of a Table's PRIMARY KEY or UNIQUE constraint, use ALTER TABLE.. DROP CONSTRAINT.

An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.

Example

DROP INDEX IX_empID ON Personnel.Employees;
GO

"Anything you build on a large scale or with intense passion invites chaos" ~ Francis Ford Coppola

Related commands:

CREATE INDEX
ALTER INDEX
Equivalent Oracle commands: DROP INDEX