Indexing is helpful when you have data which is used for 
retrieval rather than transactions. Indexing results in 
quick retrieval of data. But Indexing should be minimized 
in the database where you have lots of transactions. 
Indexes are of two types Clustered and non-clustered.

Main Purpose of Index creation is the for the Quick 
retrieval of data ie.., speed up searching, from the 
Table,where by it is created in the page index ,so that 
instead of searching out for whole records from the table 
when the query is executed it goes to page id and finds the 
index id created and retrieves out the data based on 
indexid ,so that time it takes becomes less when compared 
to non creation of index.

By default when index is not created primary key will be 
the default index.
The sysindexes table, which exists in every database, 
tracks table and index information

AFAIK

Apart from Clustered index def:

Indexes should be used to the column where frequently data 
is retrieved.

AFAIK