Need to include FKs in SQL Indexed View clustered index?

Question

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?

Answer 1

SQL Server will always be able to automatically update the view's index(es) as the source table(s) change; there's nothing extra you need to do.

You can inspect a query plan that modifies one of the source tables, you'll see there are extra operators to update the view's index(es), the same way it has to update all the applicable nonclustered indexes on the source tables when data changes.

Answer 2

If you UPDATE SmallTable in a way that touches any of its columns in the view, then SQL will perform a lookup using SmallTable's CategoryPK to find matching rows in BigTable that now need to be manifested in the view (or removed/changed). If you want to optimize that lookup, you need an index on BigTable (CategoryFK) INCLUDE (ID, SomeValue). More generally, you would want to put an index on BigTable as if the indexed view didn't exist and you were executing the View's definition but with an added WHERE SmallTable.CategoryPK = 'foo'

You don't need any extra indexes on SmallTable when the indexed view is added; just the pre-existing ones you used to optimize the UPDATE (if any) because in the process of satisfying the UPDATE on SmallTable alone, it'll pick up any values it will need down the road to fix up the View.

---

You might be thinking "if I have to put the right indexes on BigTable to make the query fast in the first place, then what's the point of the indexed view?" Well, it's a trade off, and it might just be okay for updates on SmallTable to be slow if they're rare and the indexes on BigTable are bad for external reasons.