Indexes in SQL Server

Indexes:-
Its used to change the order of data or to add metadata for improving the performance of queries.
There are two types of indexes
1)Clustered indexes
2)Non-Clustered indexes


Clustered Indexes
 1)Physically stored in order (Ascending or descending)
2) only one per table
3)When a primary key is created a clustered index is automatically created as well

4) If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5) Columns with values that will not change at all or very seldom, are the best choices.

Non-Clustered indexes
1)upto 249 non clustered indexes are possible per table
2)The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
3) Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
4)Foreign keys should be non-clustered.
If the table is under heavy data retrieval from fields other than the primary key,
one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data