In this step by step SQL Tutorial I will show you how to remove Index from database. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
Who is it for?
- People who would like to learn basics of using DROP INDEX.
- Those who are preparing for Microsoft certifications and need to cover this topic.
SQL DROP INDEX is used to remove Index from database.
Below I show you how to remove Index from database table using SSMS interface and using T-SQL.
In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I open table from which we want to remove Index. In this example it is table dbo.DimCustomer, Index called Test.
Below I will show you how to delete (DROP) INDEX from database table using Object Explorer in SSMS 2012.
I open Object Explorer, AdventureWorksDW2012 database I select Table from which I want to remove Index. I expand that table and from Indexes folder I select index I want in this case it is Test.
From drop down box I select Delete.
In the Delete Object window I click OK.
This time I will show you how to drop Index using T-SQL
Create new query and make sure that correct database is selected on the database drop dow box on the tool bar.
SYNTAX: DROP INDEX IndexName
The syntax is very simple and i below is my sample which I executed using Execute button.
NOTE: If you selected database in drop down box you could just type in query window:
DROP INDEX Employee
but it is safer to specify schema like that
DROP INDEX dbo.Employee
If you haven't selected database name or you want to execute your query against different database you can use
DROP INDEX DatabaseName.Table.IndexName
and below is example
I Execute my script and I get message that It was run successfully.
You can see that in the AdventureWorksDW2012, dbo.DimCustomer Test index no exist.
I hope that helps you to learn and use DROP INDEX statement.