We are in the process of discussing usage of indexes in SQL Server. Today it is time to discuss another type of index.

As we did in the previous occasions, let us compare following two queries.

SELECT ProductID, SalesOrderID, SalesOrderDetailID
FROM Sales.SalesOrderDetail
WHERE ProductID = 776

SELECT CarrierTrackingNumber, ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = 776

We know that Sales.SalesOrderDetail has cluster index on SalesOrderID,SalesOrderDetailID and non-clustered index on ProductID.

Following is the query execution plans for those two queries.

Though, the both queries return same set of rows, there is drastically difference in query cost. Query 1 has 1% of cost and the second has cost of 99%.

If you analyze query one, you can see that all the columns in the select and in the where clauses are covered from the non-clustered key, since non-clustered key has pointer to the clustered key.  So in query 1, database engine does not need an additional lookup to the data pages to find the data.

In the Query 2, there is column CarrierTrackingNumber which is not covered from any keys, hence needing a Key Lookup to get that data. That is the reason why second query needs more cost than the first.

OK, what if we include this column into the non-clustered key.

DROP INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] WITH ( ONLINE = OFF )
GO
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
(
	[ProductID] ASC,
	[CarrierTrackingNumber] ASC
)

Let us run the same queries again and analyze with the query execution plan.

Hurray, no key lookups and now so both query are equal on costs.

But is this an viable solution. Can you add all the columns into indexes? You can’t because of two reasons.

1. There is a limit for number of columns and indexes size. Refer Part1.

2. If you have many columns in indexes, your insert, update statements need to update your indexes which requires additional cost.

From SQL Server 2005 onwards, there is an additional feature came along with non-clustered indexes.

DROP INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] WITH ( ONLINE = OFF )
GO
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
(
	[ProductID]  ASC
)
 INCLUDE(CarrierTrackingNumber)

With this, you can create a index for columns and include other columns. In the above example, index is created on ProductID while CarrierTrackingNumber is not part of the index but included to the index and that data will be stored in the index pages. With this type of index, there is no requirement for Key Lookup.

If you analyze the execution plans again, it will be as same as before.

Let us come to an real world example.

Assume that we have following column to be included in a index.

Title nvarchar(5)

SSN nvarchar(50)

Name nvarchar(400)

If you create an index on these columns you will get a warning.

Warning! The maximum key length is 900 bytes. The index 'IX_All_Column_Index' has maximum length of 910 bytes. For some combination of large values, the insert/update operation will fail.

To avoid this you can create an index on Title and SSN and include Name for the index. Mind you, you can only do this on SQL Server 2005 onwards.