How To Drop Existing Indexes in SQL Server?

For some reason, if you want remove an existing index, you can use the DROP INDEX statement with following syntax:

CREATE INDEX table_name.index_name

The tutorial exercise below shows you how to remove the index "fyi_links_id":

USE FyiCenterData;
GO

SELECT * FROM sys.indexes WHERE object_id = (
   SELECT object_id FROM sys.tables WHERE name = 'fyi_links'
);
GO
object_id  name           index_id  type_desc     is_unique
---------  -------------  --------  ----------    ---------
421576540  NULL           0         HEAP          0
421576540  fyi_links_id   2         NONCLUSTERED  0
421576540  fyi_links_url  3         NONCLUSTERED  0

DROP INDEX fyi_links.fyi_links_id;
GO

SELECT * FROM sys.indexes WHERE object_id = (
   SELECT object_id FROM sys.tables WHERE name = 'fyi_links'
);
GO
object_id  name           index_id  type_desc     is_unique
---------  -------------  --------  ----------    ---------
421576540  NULL           0         HEAP          0
421576540  fyi_links_url  3         NONCLUSTERED  0

⇒Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials