Indexed View

  • Comments posted to this topic are about the item Indexed View

  • The wording in the explanation don't seem to match the documentation.

    http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx

    Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi,

    Thanks a lot,

    CirquedeSQLeil (12/7/2009)


    The wording in the explanation don't seem to match the documentation.

    http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx

    Explanation said that the view must not contain any deterministic functions. The referenced article states that functions must be deterministic and then continues to lay out what is not acceptable.

    Actually the explanation should be like "When you create an indexed view, the view definition must not contain any non- deterministic functions".

    However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

  • mohd.nizamuddin (12/8/2009)


    However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

    This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): http://msdn.microsoft.com/en-us/library/ms178091.aspx

    All of the aggregate and string built-in functions are deterministic.

  • Exactly, aggregate functions are deterministic. So they are allowed in indexed view. "Correct" answer is incorrect! ๐Ÿ™‚

    Deterministic and Nondeterministic Functions:

    http://msdn.microsoft.com/en-us/library/ms178091.aspx

  • vk-kirov (12/8/2009)


    mohd.nizamuddin (12/8/2009)


    However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

    This is incorrect. Check BOL (Deterministic and Nondeterministic Functions): http://msdn.microsoft.com/en-us/library/ms178091.aspx

    All of the aggregate and string built-in functions are deterministic.

    Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.

    /Hรฅkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • hakan.winther (12/8/2009)


    vk-kirov (12/8/2009)


    mohd.nizamuddin (12/8/2009)


    However, in the example the functions Count(*), SUM, MAX, MIN etc are not a deterministic function.

    This is incorrect.

    All of the aggregate and string built-in functions are deterministic.

    Count is a deterministic function, but it is not allowed anyway. You should use COUNT_BIG instead.

    I know this ๐Ÿ™‚ What I only said is: "aggregate functions are deterministic", nothing about indexed views ๐Ÿ™‚

  • Explanation corrected.

  • Wasn't sure about CharIndex since it can return a null value and which could invalidate the index statement for the view. Has anyone ever used CharIndex on an indexed view?

    Raymond Laubert
    Exceptional DBA of 2009 Finalist
    MCT, MCDBA, MCITP:SQL 2005 Admin,
    MCSE, OCP:10g

  • I tried an example containing a charindex column in SQL2005, and while it did create the index on the view, the following warning was shown when it was created.

    Warning: The optimizer cannot use the index because the select list of the view contains a non-aggregate expression.

    The execution plan for queries using the view show the use of indexes on the underlying tables, rather than the index on the view.

    So, while the system allows you to create an index on a view containing a charindex column, it doesn't appear to be of any use.

  • Thanks, that is what I would have expected. CharIndex would create a situation that invalidates the Index View and basically forces SQL to the underlying tables.

    But this does not in itself invalidate the question.

    Raymond Laubert
    Exceptional DBA of 2009 Finalist
    MCT, MCDBA, MCITP:SQL 2005 Admin,
    MCSE, OCP:10g

  • You have to add the noexpand hint to the query to use the view's index. The noexpand hint forces the optimizer to use the views data instead of the underlying tables and indexes. You have to remember that the optimizer is still cost efficient and sometimes it thinks the using the underlying data is cheaper than using the view data, which may be true in certain cases. Anyway you must use the noexpand hint to make the optimizer look at the view and its indexes only. On the flip side, you can use the expand hint to make the optimizer expand the view.

  • Thanks a lot Steve.

  • Ray Laubert (12/8/2009)


    Thanks, that is what I would have expected. CharIndex would create a situation that invalidates the Index View and basically forces SQL to the underlying tables.

    But this does not in itself invalidate the question.

    You are right it doesn't invalidate the question, but it is misleading information to everyone trying to create an indexed view with char index, and I think that is a bad.

    /Hรฅkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • What's about this question?

    In SQL 2000 - CharIndex is nondeterministic

    http://msdn.microsoft.com/en-us/library/aa214775(SQL.80).aspx

    In SQL 2005 - CharIndex is deterministic

    http://msdn.microsoft.com/en-us/library/ms178091(SQL.90).aspx

    Sorry, but question didn't precise about which version sql server was going.

    Please give me my lost point ๐Ÿ™‚

Viewing 15 posts - 1 through 15 (of 15 total)

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