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

Sql ServerSql Server-2008Sql Server-2012SsmsSql Server-2014

Sql Server Problem Overview

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.

Sql Server Solutions

Solution 1 - Sql Server

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 - Sql Server

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)


Solution 3 - Sql Server

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 - Sql Server

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 - Sql Server

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.

Solution 6 - Sql Server

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.


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionyarningView Question on Stackoverflow
Solution 1 - Sql ServerKeithView Answer on Stackoverflow
Solution 2 - Sql ServerJohnLBevanView Answer on Stackoverflow
Solution 3 - Sql ServerMCSView Answer on Stackoverflow
Solution 4 - Sql ServerashilonView Answer on Stackoverflow
Solution 5 - Sql Serverlord5etView Answer on Stackoverflow
Solution 6 - Sql ServerMaxModsView Answer on Stackoverflow