SQL Server indexes - what columns to include in index?

Question

Q1: Is it worthwhile to create indexes for foreign key columns in a SQL Server database?

Q2: If I have two related tables in a SQL Server database and I want to create an index on the foreign key column to improve performance, which columns do I need to include in the index and what type of index is best suited?

For example...

Table1

Table1ID int (Primary Key)

Table2

Table2ID int (Primary key)
Table1ID int (Foreign key)

..Would I create an Index for Table2 just with Table1ID or do I need to include the primary key (Table2ID) as well.

Q3: If I extend the example to include a third table which is related to both Table1 and Table2, do I create one index for each column or one index with both columns?

Table3

Table3ID int (Primary key)
Table1ID int (Foreign key)
Table2ID int (Foreign key)

Answer 1

Q1. Yes. (You can always remove them later if you find they are not being used regularly).

Q2. Would I create an Index for Table2 just with Table1ID? Yes. Assuming the primary intended use of the index is to help the optimiser with joins between these 2 tables. (Rather than satisfy some other query that is part of your overall query workload).

Q3. Yes. Each column with a foreign key should have a separate index to its its parent key table.

(All assuming you have an OLTP database rather than an OLAP database)

This post provides TSQL that will generate a script of all the missing Foreign key indexes in a database: TSQL: Generate Missing Foreign Key Indexes