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.

Overflight

This happens if you already have the table open in a designer - except you say you restarted SSMS. Did you go into Design Table after restarting? – Canthus

I confirmed that this is still an issue in SQL Server 2014. – Contracted

A Database diagram with the table on it will also cause this. (as it is a 'design table' view) – Utrillo

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

Contracted

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

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

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! – Dendro

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

Answer 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

Compulsion

Answer 3

Close the table if opened in the designer. Right click on Indexes for the table and select Rebuild All. This will fix it...

Hygeia

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

Kinser

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

Mountaineer

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

Crypto

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Nadiya

You know about the commenting privilege which you do not have, so well that you can even put it into words. You are aware of the rule meta.stackexchange.com/questions/214173/… . In that situation please do not decide to misuse a different mechanism (an answer) for something it is not meant for and which you are not allowed yet to do. – Goetz