1

This question already has an answer here:

Consider a join A LEFT JOIN B ON A.id = B.a_id:

  • B.a_id is nullable -- most but not all B records point to an A record.
  • A can, at most, only have one matching B record (business logic).
  • Not all A records have a match in B.

Is there a way to tell MSSQL that there will never be more than one A:B match, thus allowing it to shortcut the join once a match is found for a given A record?

I would think that such an optimization could happen if B.a_id had a unique index, but that's not possible since it's nullable.

Complicating the issue: A and B are in different databases (same MSSQL instance).

If it matters, I'm using SQL Server 2008 R2.

marked as duplicate by Paul White Sep 2 '15 at 18:49

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

Wiki answer from a comment on the question by @ypercube:


You can use a filtered index:

CREATE UNIQUE INDEX a_id_uq 
ON b (a_id) 
WHERE a_is IS NOT NULL;

See Filtered Index Design Guidelines

Not the answer you're looking for? Browse other questions tagged or ask your own question.