132

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.

Keith
  • 20,636
  • 11
  • 84
  • 125
yarning
  • 1,463
  • 2
  • 9
  • 6
  • 27
    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? – Martin Smith Nov 18 '12 at 19:01
  • I confirmed that this is still an issue in SQL Server 2014. – Keith Oct 03 '14 at 17:10
  • A Database diagram with the table on it will also cause this. (as it is a 'design table' view) – JDC Sep 02 '16 at 07:07

6 Answers6

294

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.

Keith
  • 20,636
  • 11
  • 84
  • 125
  • 17
    Thanks. Microsoft, please improve that. It is very annoying seeing `new index` greyed out. – Valamas May 03 '14 at 05:40
  • 2
    yes you do have to close table designers that have nothing to do with the table in question. grr – Simon_Weaver Oct 20 '14 at 23:10
  • 2
    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! – MikeTeeVee Nov 17 '15 at 19:55
  • This can also happen when deleting an index (causing it to be greyed out) – derekbaker783 Jul 05 '21 at 11:28
6

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

JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
4

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

MCS
  • 51
  • 3
2

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.

ashilon
  • 1,791
  • 3
  • 24
  • 41
2

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.

lord5et
  • 422
  • 7
  • 6
0

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.

MaxMods
  • 23
  • 4
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/31768346) – Jeremy Caney May 17 '22 at 18:55
  • 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 https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead . 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. – Yunnosch Sep 06 '22 at 05:51