Consider a join
A LEFT JOIN B ON A.id = B.a_id:
B.a_idis nullable — most but not all
Brecords point to an
Acan, at most, only have one matching
Brecord (business logic).
- Not all
Arecords have a match in
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
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;