The concept behind indexes is to change the order of the data (clustered index)
or to add metadata (non-clustered index) for improving the performance of queries.
SQL Server use indexes to find data quickly when a query is processed.
Clustered indexes define the physical sorting of a database table’s rows in the storage media.
For this reason, each database table may have only one clustered index.
If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table,
SQL Server automatically creates a clustered index on the primary key.
* Physically stored in order (ascending or descending)
* Only one per table
* When a primary key is created a clustered index is automatically created as well.
* 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.
* Columns with values that will not change at all or very seldom, are the best choices.
* Up to 249 nonclustered indexes are possible for each table or indexed view.
* The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
* Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
* 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.