SQL Server Management Studio won't let me add an index to a table

Question

When I right-click on the index folder in the table, the "New Index" menu item is not available. I do not understand why. I deleted all the data in the table just in case, updated and restarted SSMS, but no luck. I am using SQL Server 2012 Business Intelligence SP1 CTP.

Answer 1

Solution: Close the table and chart designers of the database and try again. If this does not help, close all windows in Management Studio.

Cause. The New Index option is disabled when the table is locked by a schema in the designer window.

Answer 2

This may be a rights issue, or you may have disconnected. Try using the code to add the index; this may solve your problem or report a more significant exception from which you can work:

create index ix_MyTable_Column1 on dbo.MyTable(Column1 asc) 

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

Answer 3

Close the table if it is open in the designer. Right-click on the indexes for the table and select Rebuild All. This will fix it ...

Answer 4

In my case, it was a view, not a table, because the view was not created using a schema binding. I changed it using a schema binding, and then I could add an index to the view. NTN.