• That's a very broad topic. Here are a few of my personal pointers

    1) Pick the clustered index first and pick it carefully

    2) Keep the clustered index as narrow as practical (note: not as narrow as possible, narrow means smaller data types and fewer columns)

    3) Usually the most frequently accessed data path is the best place for the clustered index since that's where the data is stored.

    4) Create nonclustered indexes only when you know you need them. Create as few as possible and keep them as narrow as practical too.

    5) Test, test, test, test, test, test

    6) Test some more.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning