SQL2005: Covering.Queries

We can now include nonkey columns in addition to the index key columns in our nonclustered indexes to increase query performance. For example, in the following code:

SELECT ProductCode, Name, Price
FROM Products
WHERE ProductCode BETWEEN '10000' and '20000'

A nonclustered index can be created on ProductCode to enhance the performance of the query but the Name and Price columns will need to be retrieved from the table or clustered index.

However, in SQL Server 2005, the Name and Price columns can be included in the nonclustered index as Included Columns.

CREATE NONCLUSTERED INDEX IX_Product_ProductCode
ON Product (ProductCode)
INCLUDE (Name, Price)

This is not the same as defining composite keys for the index since the columns are not part of the key but just tagged to it.

Take note that Included Columns only work with nonclustered indexes and may cause performance problems in insert, update and delete operations (if over-used) due to additional maintenance overhead.