'SQL Server Management Studio won't let me add an index to a table
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.
Solution 1:[1]
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:[2]
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:[3]
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:[4]
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:[5]
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:[6]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | JohnLBevan |
| Solution 3 | MCS |
| Solution 4 | ashilon |
| Solution 5 | lord5et |
| Solution 6 | MaxMods |