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

Question

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.

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

View more solutions

Comments

[Valamas]

Thanks. Microsoft, please improve that. It is very annoying seeing new index greyed out.

[aruno]

yes you do have to close table designers that have nothing to do with the table in question. grr

[MikeTeeVee]

I had the same issue when trying to add "Included Columns". I just now realized that for years (a Decade exactly) all I had to do was close the Table Designer and right-click on the Index to select "Properties" in order to add/edit them.... All these years wasted on writing custom scripts. face-palm I want my youth back!

[Al Kepp]

Wow, this saved my day! +1

[derekbaker783]

This can also happen when deleting an index (causing it to be greyed out)