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...