Post reply

Indexed View

  • December 7, 2009 at 11:37 pm

    #218328

    Comments posted to this topic are about the item Indexed View

  • December 7, 2009 at 11:40 pm

    #1089036

    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

  • December 8, 2009 at 12:54 am

    #1089047

    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.

  • December 8, 2009 at 2:31 am

    #1089071

    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.

  • December 8, 2009 at 3:33 am

    #1089091

    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

  • December 8, 2009 at 5:39 am

    #1089129

    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

  • December 8, 2009 at 6:38 am

    #1089164

    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 🙂

  • December 8, 2009 at 7:24 am

    #1089214

    Explanation corrected.

  • December 8, 2009 at 7:28 am

    #1089222

    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

  • December 8, 2009 at 8:40 am

    #1089281

    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.

  • December 8, 2009 at 9:34 am

    #1089337

    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

  • December 8, 2009 at 4:06 pm

    #1089585

    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.


    My blog: http://jahaines.blogspot.com

  • December 8, 2009 at 9:04 pm

    #1089628

    Thanks a lot Steve.

  • December 9, 2009 at 1:46 am

    #1089681

    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

  • December 10, 2009 at 7:50 am

    #1090559

    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