Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 210

How it works...
After observing the new execution plan for the SELECT query, it is now clear that the Key
Lookup overhead is removed successfully by using a covering index. Actually, in this new
situation, SQL Server Optimizer wouldn't need to go to a clustered index to get values of the
non-key column, as the non-clustered index now covers both OrderID and OrderDate .
There's more...
While creating a non-clustered covering index, keep in mind that a maximum of 16 columns
is allowed in one index, and the total size of index columns (key columns) should not exceed
900 bytes.
Increasing performance by including
columns in an index
The concept of included columns in indexes was introduced in SQL Server 2005 and is also
available in SQL Server 2008 and 2012. We can include non-key columns in a non-clustered
index, as they are not counted in its index size.
There is a limitation wherein the maximum number of columns allowed is 16 and the
maximum size of the index key column allowed is 900 bytes, so it is not a good idea to
have an index with many unnecessary or non-key columns.
Only include the key column in the Index part, and to avoid the lookup of a non-key column,
keep another non-key column in the INCLUDE part of a non-clustered Index. This is because
any column given in the INCLUDE part of a non-clustered index doesn't fall under the
limitations discussed previously.
Getting ready
In the Increasing performance by a covering index section, we had an OrderId column as a
part of the main non-clustered index. However, OrderID is not the key column, as we have
not used it in the search condition ( WHERE ). So, OrderId could be under the INCLUDE part of
the non-clustered index instead of being a part of the main index. By doing so, we can reduce
the size of the non-clustered index.