SQL Indexes

Indexes allow SQL Server to quickly find rows in a table based on key values.
There are two types of indexes in SQL Server, clustered and nonclustered indexes.

Clustered Indexes
A clustered index is an index whose leaf nodes, that is the lowest level of the index, contain the actual data pages of the underlying table. Hence the index and the table itself are, for all practical purposes, one and the same. Each table can have only one clustered index. When a clustered index is used to resolve a query, SQL Server begins at the root node for the index and traverses the intermediate nodes until it locates the data page that contains the row it's seeking.
Many database designs make prolific use of clustered indexes. In fact, it is generally considered a best practice to include a clustered index on each table.
Lets’ consider an example. In the Customers table has a clustered index defined on the Customer_ID column. When a query is executed that searches by the Customer_ID column, SQL Server navigates through the clustered index to locate the row in question and returns the data. This can be seen in the Clustered Index Seek operation in the query’s Execution Plan.
SELECT Customers.Last_name,
Customer.First_name,
Customers.Email_Address
FROM
Customers
WHERE
Customers.CustomerID = 123

Nonclustered indexes
Nonclustered indexes use a similar methodology to store indexed data for tables within SQL Server. However in a nonclustered index, the lowest level of the index does not contain the data page of the table. Instead, it contains the information that allows SQL Server to navigate to the data pages it needs. For tables that have a clustered index, the leaf node of the nonclustered index contains the clustered index keys. In the previous example, the leaf node of a nonclustered index on the Customers table would contain the Customer_ID key. If the underlying table does not have a clustered index (this data structure is known as a heap), the leaf node of the nonclustered index contains a row locator to the heap data pages.
For example, a nonclustered composite index(ix_Customer_name) is been created on the Customers table encompassing the First_name, Last_name columns.
In this case when a query that searches by customer last name is executed, the SQL Server query optimizer choses to use the ix_Customer_Name index to resolve the query.
Using Nonclustered indexes
As illustrated in the preceding example, nonclustered indexes may be employed to provide SQL Server with an efficient way to retrieve data rows. However, under some circumstances, the overhead associated with nonclustered indexes may be deemed too great by the query optimizer and SQL Server will resort to a table scan to resolve the query.

Covering Indexes
When Key Lookups are detrimental to performance during query resolution for large result sets, we have to consider a query that does not require a Key Lookup. We can modify the previous query so that it no longer selects the Email_Address column.
SELECT Customers.Last_name,
Customer.First_name,
Customers.Email_Address
FROM
Customers
WHERE
Customers.First_name = 'SMITH'
The new execution plan has been streamlined and only uses the ix_Customer_Name nonclustered index which improves the performance of the query considerably.
The observed improvement is due to the fact that the nonclustered index contained all of the required information to resolve the query. No Key Lookups were required. An index that contains all information required to resolve the query is known as a "Covering Index"; it completely covers the query.