Indexed View

  • mohd.nizamuddin

    Ten Centuries

    Points: 1328

    Comments posted to this topic are about the item Indexed View

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • mohd.nizamuddin

    Ten Centuries

    Points: 1328

    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.

  • vk-kirov

    SSCertifiable

    Points: 7686

    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.

  • ser_s

    Valued Member

    Points: 65

    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

  • SQLWinther

    SSCertifiable

    Points: 5946

    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

  • vk-kirov

    SSCertifiable

    Points: 7686

    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 ๐Ÿ™‚

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

  • Ray Laubert

    SSCrazy

    Points: 2910

    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

  • Mike Mullen

    Hall of Fame

    Points: 3248

    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.

  • Ray Laubert

    SSCrazy

    Points: 2910

    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

  • Adam Haines

    SSC-Insane

    Points: 23197

    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.

  • mohd.nizamuddin

    Ten Centuries

    Points: 1328

    Thanks a lot Steve.

  • SQLWinther

    SSCertifiable

    Points: 5946

    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

  • stoklosa

    Old Hand

    Points: 367

    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 16 total)

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