Two examples are:
Is this correct and are there other important use cases?
Can SQL Server recommend fields to index based on usage patterns ?
Those are about the only reasons to index.
Since "usage pattern" is very hard to quantify -- and it varies -- it's impossible to make a blanket statement on indexes.
An index that is heavily updated (or deleted) becomes more cost than benefit.
An index that is rarely used may be most costly to maintain than the few queries that use it.
It requires active experimentation to determine if the mix of indexes is optimal for the current usage pattern of your database. That means looking at query execution plans to see what's used, and looking at slow queries to see what might be missing.
You will know if your performance is not good enough.
The things you mention are parts of the solution already. In addition to those two, I would also add:
That helps tremedously when doing JOINs and it's not done automatically by SQL Server (contrary to a common place urban myth floating around, SQL Server has never automatically put any indices on foreign keys).
Yes, as of SQL Server 2008, if you include the Actual Execution Plan in your query in SQL Server Management Studio, it can give you some hints as to what indices might help.
BUT CAREFUL: with this, you're just tuning a single SQL query. You can tune that to the moon and back, but still have awful performance, since a single query often isn't the whole story, and tuning one query to perform extremely well can negatively impact the rest of the system.
To REALLY know what to tune and how to tune it, you'll need to capture actual real life usage data, using SQL Server profiler traces, over an extended period of time and at various times of the day of your system being in use, and examine those.
Read up on SQL Server Profiling:
Marc
Sorting and querying are the "must be" of almost all indexes at design time. You have to be careful of wich of the indexes for a table you do the clustered one for not to introduce insert/update delays.
Appart of that, you will discover if you need more indexes doing performance traces of your system in production, and detecting wich are the sql commands that last more time to be executed.
Your best bet is to start adding indexes if your queries are slow. For most small/medium databases, spending time figuring out indexes to apply early is not a good use of your time.
Columns that are more frequently accessed with minimum updation with less redundant data should be made index. If there is not problem of storage then database should be heavily indexed.
You should almost always index foreign keys (Primary keys are indexed automatically but Fks are not). FKs are generally used in joins and thus should be indexed.