How does SQL Server know what index to use?
Answer originally left as a comment by i-one:

Your query references ProductId, VariantId, StoreId, ChainId and LocalInventory columns.

All of them except ChainId are declared in the non-clustered index definition.

ChainId is included in the non-clustered index implicitly as part of the clustered index key.

So, the non-clustered index is covering for this query.



The nonclustered index contains the clustered index keys because SQL Server must be able to locate the base table row from the index entry.

From the SQL Server Index Architecture and Design Guide:


  If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.