indexes aren't the lawn darts of the database world...
you dont just thow them around and hope that they fit because more than likely they wont. indexes are additional architectural elements added to tables (or views) to decrease the amount of time that querying takes. there are known techniques that you can use to evaluate the "usefulness" of an index. the sql query analyzer contains a graphical execution plan tool that will indicate the use or the lack of use as well as the type of index operation being performed.
remember that not only are there index operations being performed based on query SARGS (hey trotsky!!!) but they also affect join operations as well.
consider the basic two table join between a unique PK column and a Dense FK column. by placing a nonclustered index on the fk column you create a sorted version of the Fk column thereby decreasing the time it takes to perform the join. (this is the typical result and not the 100% result)
indexes have their bad sides as well. consider the lopsided levels of an index based on a unique column ( in oracle they call this right side heavy). in addition index performance is based on statistics and those statistics have to be updated and this is a cost added to your queries. also, dont forget that you will have to rebuild your indexed from time to time based on fragmentation of the leaf level.
indexes are so misunderstood by developers and dbas alike.. i suggest you find a series of articles on indexes by kalen delaney at www.sqlmag.com or look for index articles by Kimberly Tripp.