Results 1 to 14 of 14

Thread: Index On View

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Index On View

    Hi,
    Can we define Index on view?




    Thanks,
    Rahul Jha

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    thnkx jezemine

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DBA_Rahul
    Hi,
    Can we define Index on view?

    Thanks,
    Rahul Jha


    Are you playing us?

    I mean where do you come up with these questions

    For example, wouldn't you be more worried about appropriate indexes on the tables first?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Was worried about indexes on the table first Brett. And once done with that want to check with the indexed view. Who knows that might again improve the performance. But I was amazed that why are you asking this........


    No, m not playing with any one Brett.

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    I am here to help myself from the support of fellow members.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes Rahul, but this information is easily found in Books Online or a simple google search. The forum does not exist to copy/paste the relevant documentation for you, or do your searches for you. Your first resource should always be Books Online. Your second resource should be a google search, or a search of existing threads on dbforums. Only after exhausting these resources should you post your question on the forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by DBA_Rahul
    Was worried about indexes on the table first Brett.
    Not necessarily. As far as I understand the indexed views, SQL Server will use the indexes when the view is queried directly, bypassing the table. An indexed view in SQL Server seems to be very similar to a materialized view in Oracle. So I'd assume that an indexed view can be quite fast even if the underlying tables don't have any indexes defined.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Never, ever had to create an indexed view

    BOL

    In SQL Server 2000, indexes also can be created on computed columns and views. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.

    The UNIQUE or PRIMARY KEY may contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic, precise, and must not contain text, ntext, or image columns. For more information about determinism, see Deterministic and Nondeterministic Functions.

    Creation of an index on a computed column or view may cause the failure of an INSERT or UPDATE operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, although computed column c in the following table will result in an arithmetic error, the INSERT statement will work:

    CREATE TABLE t1 (a int, b int, c AS a/b)
    GO
    INSERT INTO t1 VALUES ('1', '0')
    GO

    If, instead, after creating the table, you create an index on computed column c, the same INSERT statement now will fail.

    CREATE TABLE t1 (a int, b int, c AS a/b)
    GO
    CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
    GO
    INSERT INTO t1 VALUES ('1', '0')
    GO

    The result of a query using an index on a view defined with numeric or float expressions may be different from a similar query that does not use the index on the view. This difference may be the result of rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

    To prevent SQL Server from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, setting any of the listed options incorrectly will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT.

    Restrictions on indexed views
    The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.

    The SELECT list may not include asterisks (*), 'table.*' wildcard lists, DISTINCT, COUNT(*), COUNT(<expression>), computed columns from the base tables, and scalar aggregates.

    Nonaggregate SELECT lists cannot have expressions. Aggregate SELECT list (queries that contain GROUP BY) may include SUM and COUNT_BIG(<expression>); it must contain COUNT_BIG(*). Other aggregate functions (MIN, MAX, STDEV,...) are not allowed.

    Complex aggregation using AVG cannot participate in the SELECT list of the indexed view. However, if a query uses such aggregation, the optimizer is capable of using this indexed view to substitute AVG with a combination of simple aggregates SUM and COUNT_BIG.

    A column resulting from an expression that either evaluates to a float data type or uses float expressions for its evaluation cannot be a key of an index in an indexed view or on a computed column in a table. Such columns are called nonprecise. Use the COLUMNPROPERTY function to determine if a particular computed column or a column in a view is precise.

    Indexed views are subject to these additional restrictions:

    The creator of the index must own the tables. All tables, the view, and the index, must be created in the same database.


    The SELECT statement defining the indexed view may not contain views, rowset functions, inline functions, or derived tables. The same physical table may occur only once in the statement.


    In any joined tables, no OUTER JOIN operations are allowed.


    No subqueries or CONTAINS or FREETEXT predicates are allowed in the search condition.


    If the view definition contains a GROUP BY clause, all grouping columns as well as the COUNT_BIG(*) expression must appear in the view's SELECT list. Also, these columns must be the only columns in the CREATE UNIQUE CLUSTERED INDEX clause.
    The body of the definition of a view that can be indexed must be deterministic and precise, similar to the requirements on indexes on computed columns. See Creating Indexes on Computed Columns.

    Permissions
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have, occasionally. Its a handy way to implement unique constraints on nullable columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I have, occasionally. Its a handy way to implement unique constraints on nullable columns.

    ummmmmmmmm

    what kind of constraints that can't be done at the table level?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Column values must be unique or null. Allow multiple NULLs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's the schema binding stuff i always found awkward and led me to shun indexed views.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Allow multiple NULLs.

    On a Unique INDEX?

    You sure?

    Post some sample code
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •