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

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.
Subpages (1): Tracing Session / System