Can I optimize a left join for a single match? [duplicate]

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.

_{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