FK on NON Primary Keys

Question

Is there any performance problem / difference if I have one Foreign Key that points to another table Indexed Fields that is NOT the PK ?

Something like

DECLARE @T1 TABLE 
(   
    T1_ID INT IDENTITY PRIMARY KEY,    
    T1_SOMEID INT NOT NULL,    
    OfficeID VARCHAR(3) NOT NULL,   
    Data VARCHAR(200) NOT NULL 
);

DECLARE @T2 TABLE 
(   
    T2_ID INT IDENTITY PRIMARY KEY,    
    T1_SOMEID_FK INT NOT NULL,    
    OfficeID VARCHAR(3) NOT NULL,   
    OtherData VARCHAR(100) NOT NULL 
);

QUESTION:
Is there a performance drop when I run a query that looks like this:

SELECT t1.T1_ID, t1.Data, t2.T2_ID, t2.OtherData 
FROM @T1 t1 
    INNER JOIN @T2 
       ON t1.T1_SOMEID = t2.T1_SOMEID_FK 
          AND t1.OfficeID = t2.OfficeID

Answer 1

Two things:

1 - You still need to have a UNIQUE constraint to use a field as a foreign key.

2 - In your example you WILL have a performance decrease, because by default a PK is also a clustered index. A non-clustered index on your FK field will speed the JOIN but you will still need to pay the cost for a bookmark lookup since the other fields aren't at the leaf level of the index. If you INCLUDE(otherdata) in your NC index then it should work fine (assuming that ID is your clustered index.

Answer 2

Assuming you explicitly choose the clustered index for the table and have ensured the columns do not accept nulls (and I strongly suggest that you do both), there is no performance implications of a foreign key referencing a UNIQUE constraint rather than the PRIMARY KEY.

Declaring a FK that references a PRIMARY KEY allows you to omit the PK columns in the declaration (no big deal).

PRIMARY KEY implies the cluster index but doing so complicates matters by mixing logical and physical considerations e.g. PRIMARY KEY (c1, c2) may performance worse than PRIMARY KEY (c2, c1).

PRIMARY KEY implies the columns involved are nullable (no big deal to declare this explicitly).

Something perhaps to consider is that some software (ORM, query builder, etc) will assume that "FK references PK" is the only possibility. Ditto users :)