Is it possible to remove a non clustered index from a table's column?

Question

I just want to know if it is possible to remove a non clustered index from a tables column .I recently created a required non clustered index on a column. So I just want to delete this index so that I can get the execution plan before and after adding the index to the table. I wanted to delete it via query. Is it possible?

Answer 1

There is a DROP INDEX command;

DROP INDEX table_name.index_name

Should work.

It's also possible to disable the index. The pros and cons are discussed here, but the effective point from this article is:

The biggest difference between disabling and dropping an index is whether the metadata and statistics are persisted. If disabled, they are. If dropped, they are not. Make sure you carefully weigh your options before performing either action, and always have a way to recreate the index available.

So in this instance you may want to simply disable the index, collect your stats, then re-enable the index, via:

Disable:

ALTER INDEX index_name ON schema.table_name DISABLE

Enable:

ALTER INDEX index_name ON schema.table_name REBUILD

Answer 2

USE [Database_Name_here]
GO
DROP INDEX [<The_Index_Name>]
ON [Schema_Name].[<Table_Name>]
GO

After running it, you can cross check in the object explorer tree, go to that table in object explorer at left, Then check Indexes folder, that index wouldn't be visible after dropping.