Setting Indexes on Views

Posted on 2005-04-21
Last Modified: 2011-10-03
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?
Question by:Plecoman
    LVL 28

    Expert Comment

    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.

    Author Comment

    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?
    LVL 28

    Assisted Solution

    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.
    LVL 34

    Assisted Solution

    by:Brian Crowe
    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.
    LVL 75

    Accepted Solution

    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.

    Author Comment

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how the fundamental information of how to create a table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now