I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can be seen by deleting a row from a parent table with 254 or more incoming foreign key references: dbfiddle link.
There are three different counts displayed in the operator details:
- Foreign Key References Count is the number of incoming foreign keys.
- No matching Indexes Count is the number of incoming foreign keys without a suitable index. Verifying that the updated or deleted table won't violate that constraint will require a scan of a child table.
- I don't know what Partial Matching Indexes Count represents.
What is a partial matching index in this context? I wasn't able to get any of the following to work:
- Filtered indexes
- Putting the foreign key column as an
INCLUDEcolumn for an index
- Indexes with the foreign key column as the second key column
- Single column indexes for multiple column foreign keys
- Creating multiple covering indexes to enable an "index join" plan for a multiple column foreign key
Dan Guzman pointed out that multiple column foreign keys can match indexes even if the index keys are in a different order than the foreign key columns. His code is here in case someone is able to use it as as starting point to figure out more about partial matching indexes.