• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

Setting Indexes on Views

I have recently upgraded my SQL server from Standard to Enterprise edition. However the Manage Indexes feature is still grayed out when trying to set an index on a view. Any thoughts?
3 Solutions
Here's what I got from Books Online:

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes.

A view must meet these requirements before you can create a clustered index on it:

The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

The view must not reference any other views, only base tables.

All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

The view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.

User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

Tables and user-defined functions must be referenced by two-part names. One-part, three-part, and four-part names are not allowed.
PlecomanAuthor Commented:
Thank you, I also read this, but it seams the option is not available to me at all at this time. I tried creating a very simple view and SQL will not allow me to create the index on that view, grayed out. Other thoughts?
As far as I know, one of the most important part when creating the view is the WITH SCHEMABINDING clause.  Can you please post your DDL for the view.
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Brian CroweDatabase AdministratorCommented:
You need to do it via T-SQL.  Open the view properties and add the index after your CREATE VIEW statement using the information that francisco outlined above.
Anthony PerkinsCommented:
As everyone has suggested, you really need to get weaned from Enterprise Manager.  However, if you can't than just do the following:
1. Go to the design mode and click on "Bind to Schema"
2. Save.
3. Refresh the views.
4. Indexes are now enabled.
PlecomanAuthor Commented:
Thank you

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now