What is a "Partial Matching Index"?

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 INCLUDE column 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.

I spoke to people much smarter than I and we will be documenting this soon™.

The actual definition of this, in the interim is:


PartialMatchingIndexCount reflects the number of references that could
be checked using index seeks, but the index key does not cover all the
columns being checked. For example, the corresponding
ForeignKeyReferenceCheck elements contain both a Seek Predicates and a
Predicate element.

In addition:


If this number is greater than 0, there is a potential performance issue, in case the partial matches result in large numbers of rows.

StackExchange Database Administrators Q#192526, answer score: 13