SQL Server FAQ - DROP INDEX - Removing Existing Indexes

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Drop Existing Indexes?

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

(Continued on next topic...)