BUS7 Consulting

http://msdn.microsoft.com/en-us/library/ms172984%28v=sql.100%29.aspx

Create Highly-Selective Indexes

Create Multiple-Column Indexes

Avoid Indexing Small Tables

We recommend that you always create indexes on primary keys. It is frequently useful to also create indexes on foreign keys. This is because primary keys and foreign keys are frequently used to join tables. Indexes on these keys lets the optimizer consider more efficient index join algorithms. If your query joins tables by using other columns, it is frequently helpful to create indexes on those columns for the same reason.

When primary key and foreign key constraints are created, SQL Server  automatically creates indexes for them and takes advantage of them when optimizing queries.

Remember to keep primary keys and foreign keys small. Joins run faster this way.