CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012ssmssql-server-2014
yarning
yarning

Reputation: 1533

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

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.

Upvotes: 139

Views: 51827

Answers (6)

MaxMods
MaxMods

Reputation: 23

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.

Upvotes: 0

lord5et
lord5et

Reputation: 500

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.

Upvotes: 3

Keith
Keith

Reputation: 21264

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.

Upvotes: 309

ashilon
ashilon

Reputation: 1941

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.

Upvotes: 3

MCS
MCS

Reputation: 51

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

Upvotes: 4

JohnLBevan
JohnLBevan

Reputation: 24470

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

Upvotes: 6

Related Questions