Menu
Click the plus sign to expand the table on which you want to enable an index. Click the plus sign to expand the Indexes folder. Right-click the index you want to enable and select Rebuild. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to rebuild grid and click OK.
To create a new index for a table, you use the CREATE INDEX statement as follows: CREATE INDEX index_name ON table_name(column1[,column2,...]) Second, specify the name of the table followed by one or more indexed columns surrounded by parentheses.
Analysis: SQL Server might ignore the index if the range is too wide. For example, these two queries will probably hit the index on the LastUpdated column in a 300 million rows table because the range is very narrow.
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.
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
Close the table if opened in the designer. Right click on Indexes for the table and select Rebuild All. This will fix it...
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With