Index basics

  An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure(B-tree) that enable SQL Server to find the row or rows associated with these key values quickly and efficiently.
  Types of Index:
  Clustered:
     Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. there can be only one clustered index per table.
     When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap. 

  Unclustered
     Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. 
     The pointer from an index row in a nonclustered index to a data row is called a row locator.

Indexes and Constrains
Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns.
 
How Indexes Are Used by the Query Optimizer
The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select.