I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg:
CREATE VIEW BigTableSubset WITH SCHEMABINDING AS SELECT b.ID, b.SomeValue from BigTable b INNER JOIN SmallTable s on (b.CategoryFK = s.CategoryPK) where s.Type = 'Blah' CREATE UNIQUE CLUSTERED INDEX PK_BigTableSubset ON BigTableSubset (ID) INCLUDE (SomeValue)
(BigTable.ID is the PK on BigTable)
Query performance is substantially increased (since the subset of bigtable required is already stored), but I'm concerned to ensure that SQL can update the index itself in the most optimal way when either of the source tables is updated.
So the question is: do I need to include SmallTable.CategoryPK in the view's clustered index to ensure this, or will SQL do this automagically as part of the index structure? Or to put it another way, how does SQL identify what pages in the index need updating every time a 'left hand' (SmallTable) table updates, and do I need to do anything to facilitate that?