Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
well these are dummy tables, the real ones have way many more fields, so if i understand well, you are suggesting to put all fields on the Index (which is not viable). can u look at this thread and let me know ur thoguhts ? dba.stackexchange.com/questions/12055/… –  Developer Feb 2 '12 at 19:57
2  
+1, but: in SQL Server an FK can refer to unique index too, it does not have to be a unique constraint –  AlexKuznetsov Feb 2 '12 at 20:03
    
Well not to split hairs but isn't a unique constraint just a unique index? You define the constraint but SS puts the index on in the background. –  JNK Feb 2 '12 at 20:04
2  
A unique index can have included columns to avoid lookups. A unique constraint can't, and will actually use a unique index to enforce the constraint anyway. So in this scenario, a unique index without a unique constraint would be better. –  Rob Farley Feb 2 '12 at 23:21
add comment

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 :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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