Post reply

why can''t i set indexes on views, i have enterprise edition. but its grayed out when I try

  • February 10, 2006 at 8:28 am

    #171126

    why can't i set indexes on views, i have enterprise edition. but its grayed out when I try

  • February 10, 2006 at 9:27 am

    #620195

    It's not enough having enterprise edition, you also need to fullfil the criteria that is described in books online.

    just open it up and type indexed view, you should then see what you're doing wrong.  If you still think it should work because you meet all of the criteria then feel free to post your DDL and we'll take a look for you.

    Mike

  • February 10, 2006 at 1:25 pm

    #620274

    Option #2: T-SQL.  "It's greyed out when I try" sounds like you're trying this in Enterprise Manager.  Fire up Query Analyzer and:

    1.  Ensure all the tables you're referencing were created with ANSI_NULLS ON.  Use the OBJECTPROPERTY function (Note: you can only reference tables, not views or UDFs, from an indexed view):

      SELECT OBJECTPROPERTY(OBJECT_ID('MyTable'), 'IsAnsiNullsOn')

    2. Make sure you are setting all the SET options (the magic 6 on + 1 off) listed in BOL (QUOTED_IDENTIFIER, etc.) before you issue the CREATE VIEW statement:

      

    SET QUOTED_IDENTIFIER, ANSI_NULLS, (etc., look it up) ON

    3.  Check that your view follows the rules layed out in BOL - such as no subqueries, no outer joins, no table.* - there are a lot of them.

    4. Create the view using WITH SCHEMABINDING:

    CREATE VIEW dbo.MyIndexedView AS
  SELECT Col1, Col2, Col3
    FROM dbo.MyTable
  WITH SCHEMABINDING

    5.  Then you can create your index:

    CREATE UNIQUE CLUSTERED INDEX MyNewIndex ON dbo.MyIndexedView(Col1, Col2)

    6.  Check your results by selecting from the view in a way that would use the index and looking at the query plan:

      SELECT Col1, Col2
    FROM dbo.MyIndexedView
   ORDER BY Col1

    ...highlight that in QA and hit [ctrl]+L.  The graphical query plan should show reads from a clustered index.

      The problem is that most advanced techniques are not possible from Enterprise Manager.  It just wasn't built for that.  Real development requires using development tools, such as Query Analyzer or a number of third-party dev tools out there, and some T-SQL.

    -Eddie

    Eddie Wuerch
    MCM: SQL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply