SQL Server Performance

Indexed views on frequently updated data

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wentu, Apr 10, 2012.

  1. Wentu New Member


    MSDN says: "Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index."

    What should I consider as "underlying data" that must be infrequently updated?
    1) ALL the data in the database ?
    2) ALL the data in the TABLE from which the view takes its data?
    3) ONLY the data in the TABLE that are required for the view ? In other words: if the TABLE contains "Employee" (doesnt change much) and "LastAcivityTimeStamp" (changes very often) but in my view I only retrieve "Employee", should I consider the use of an indexed view?

    Thank You
  2. Shehap MVP, MCTS, MCITP SQL Server

  3. Wentu New Member

    Thank You Shehap for your answer.
    I probably couldn't convey the exact meaning of my request.
    I'll try with another example.
    I have TableA with a field IdA used as PK and primary index. Another field is IdB used as a FK
    I have a second table TableB with IdB as PK and primary index. Besides, I have two fields, let's call them "AlwaysTheSameField" and "OftenChangingField".
    A view that i want to create is something like
    SELECT IdA, IdB, AlwaysTheSameField FROM TableA Inner Join TableB on TableA.IdB = TableB.IdB

    An index of this view would probably have IdA and IdB as fields.

    Now, given the MSDN advices that i quoted before, since this index (if i understood correctly) needs to be updated, it may happen that the cost of keeping it updated exceeds the advantage of the index.
    What I am asking is: will the index on the view need frequent updates because of the presence of OftenChangingField in the TableB, even if this field is not required by the view ? Let's say that, thankx to other considerations, I am pretty confident that, if there were no "OftenChangingField", the index on the view would be a good thing. Is the presence of "OftenChangingField" influencing this or not ?

    Thank You very much for Your help

  4. FrankKalis Moderator

Share This Page