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:

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:

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)

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Solution 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:

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.