is it necessary to specifically define a 'Foreign Key' with 'References' to the appropriate primary key table primary key column
Yes. You need to specify the table and column.
If necessary to specifically define a Foreign Key
No. Just because SQL Server is a relational database does not mean you need to relate your tables.
does this speed up data retrieval when joining the tables
No. Defining foreign keys will not speed up data retrieval.
if you join them without specifying the Foreign Key, does that make data retrieval not as efficient?
Foreign keys are not meant to improve performance. In fact, it's possible that they will actually cause performance problems if you are not careful.
Foreign keys are meant to keep your data nice and clean. For example, suppose you had a gender column in your table. The valid options would be NULL, M and F. You probably wouldn't want to see any other values in the column, right? Well, if you had a lookup table for gender, you could create a foreign key which will enforce the lookup table values. Gender is a contrived example, but hopefully you get the point.
Foreign keys usually reference data in a lookup table. For example, suppose you had a warehouse with tons of boxes everywhere. You might have a database to keep track of box locations. You wouldn't want someone to enter a "box location" in to the table that doesn't exist, because it's likely that the application wouldn't return this data and then you'll have a real mess on your hands.
My rule of thumb is... if you can add a foreign key to a table, you should.
One common mistake people make is.... they don't add an index for the foreign key.
Using the Box, Location, BoxLocation example....
Suppose the box table has a Primary Key of BoxId, the location table has a primary key of LocationId. The BoxLocation table would have BoxId and LocationId, right. Each of this columns would reference the other table. You should make sure you have separate indexes in the BoxLocation table for each column. The index will speed up the joins (not the foreign key).
Make sense?
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom