SQL Server Management Studio won't let me add an index to a table
Sql ServerSql Server-2008Sql Server-2012SsmsSql Server-2014Sql Server Problem Overview
When I right click on the indexes folder in the table the "New Index" menu item is grayed out. I don't understand why. I've deleted all data in the table just in case, and refreshed and restarted SSMS, but no luck. I'm using SQL Server 2012 Business Intelligence SP1 CTP.
Sql Server Solutions
Solution 1 - Sql Server
Solution: Close your table designers and database diagrams and try again. If that doesn't help, close all windows in Management Studio.
Cause: The "New Index" option gets disabled when the table is schema-locked by the designer window.
Solution 2 - Sql Server
It could be a rights issue, or perhaps you've become disconnected. Try using code to add the index; that may resolve your issue, or report a more meaningful exception for you to work from:
create index ix_MyTable_Column1
on dbo.MyTable(Column1 asc)
Solution 3 - Sql Server
Close the table if opened in the designer. Right click on Indexes for the table and select Rebuild All. This will fix it...
Solution 4 - Sql Server
In my case, which was a view, not a table, it was because the view wasn't created with Schema Binding. I altered it use Schema Binding and then I could add the index to the view. HTH.
Solution 5 - Sql Server
Table does not have a clustered primary key as required by the spatial index. Make sure that the primary key column exists on the table before creating a spatial index.
Solution 6 - Sql Server
Since I can't comment on the top post, I'm going to add an answer.
In my case, I'm trying to create an index via a query. I was getting an error returned that the table didn't exist or I didn't have permission. When I closed all tabs and exited SSMS completely, my error was resolved completely when I reopened SSMS.