Hello 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 W.
First, welcome to Forums Creating index on views is based on the nature of view usage inside other SPs or UDF or even Ad-hoc queries where Join columns + where Columns are highly considerable for indexing wise For more details about key columns + include columns of indexes , you might have a look at http://www.sqlserver-performance-tu...w/12927042-towards-t-sql-queries-of-0-sec-6-6-
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 W.
No, since the "OftenChangingField" is not present in the indexed view definition and therefore not in the views result set, it has no impact here. You might want to have a look at this article: Improving Performance with SQL Server 2005 Indexed Views