Features

1) Improves performance
2) Could be built on one or more  columns in a table or view also
   these are automatically created for primary keys and unique
   constraints


We have two types of indexes

1) Clustered Index.
2) Non-Clustered Index



Clustered Index

Leaf Node contain actual data pages, data is sorted and stored based on
index key.

Clustered index are normally designed for a table which has a query which
returns larger amount of data for example queries having where data between
certain range


Nonclustered Index.

Leaf nod contains the pointer to the actual data pages.
Non-Clustered index are normally designed for a table which have query
that returns small matches for example for queries having where="value"


Maintaining our indexes.

Rebuilding and re-organizing
Rebuild : Drops the index and recreated the index
Reorganize : Just re-organizes the leaf nodes

You should use re-org when fragmentation is less than 30-40 %

In enterprise version we have option of online index rebuilding i.e index is
accessible during the rebuilding process.