Need to include FKs in SQL Indexed View clustered index?

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?

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.

StackExchange Database Administrators Q#56972, answer score: 4