Developers delete an index when they found that the Index hinders overall performance of INSERT, UPDATE, and DELETE clauses or if they no longer need it. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to delete an index, they should follow the next steps:
1. In Object Explorer, right-click the table with indexes they want to delete and click Design (Modify in SP1 or earlier).
2. From the Table Designer menu, click Indexes/Keys.
3. In the Indexes/Keys dialog box, select the index they want to delete.
4. Click Delete.
The index is deleted from the database when they save their table.