Let's consider the following scenario: I've a "master table" with a "detail" table. The detail table have just a foreign key pointing to the primary one ( not a primary key ). This is the schema that NHibernate generates for me when I map a simple bag. My question is, does the FK itself on the detail table suffices to have queryes without full scan? In other world, having or not having the FK defined on the detail table, does change the performance? I guess yes, but I don't know if I'm right, or where to find a source to explain it.
Asked
Active
Viewed 39 times
0
-
1this is a duplicate: http://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance – Felice Pollano Jan 05 '12 at 10:05
1 Answers
1
In both cases (FK or no FK) you'll have to create an index on the FK field in the details table to prevent a table scan. In sql server, when creating an FK constraint an index is not created automatically.
See MSDN, "Indexing FOREIGN KEY Constraints".
Gert Arnold
- 105,341
- 31
- 202
- 291