Special Indexing and Storage Features - Pro SQL Server Internals - page 80

SQL Server 2005 introduced a new way of making covering indexes by storing columns in the index without
adding them to the index key. The data from included columns are stored on the leaf level only and does not affect the
sorting order of the index rows. SQL Server did not need to move rows to different places in the index when included
columns were modified. Included columns are not counted towards the 900 bytes index key size limit, and you can
store LOB columns if needed.
Figure 4-1 illustrates the structure of an index with included columns, defined as CREATE INDEX IDX_Customers_Name
ON dbo.Customers(Name) INCLUDE(DateOfBirth) on the table, which has CustomerId as the clustered index.
Figure 4-1. Structure of an index with included column
Let's look at how an index with included columns can help us with query optimization. We will use table
dbo.Customers , which we created and populated with the data in Listing 3-3 in the previous chapter. That table has a clustered
index on the CustomerId column and a composite nonclustered index on the (LastName, FirstName) columns.
Let's select data for a customer with last name Smith . We will run two queries. In the first case, we allow SQL
Server to choose the execution plan by itself. In the second case, we will force SQL Server to use a nonclustered index
with an index hint. The code to do this is shown in Listing 4-1. Figure 4-2 shows the execution plans for the queries.
Listing 4-1. Selecting data for a customer with the last name 'Smith'
select CustomerId, LastName, FirstName, Phone
from dbo.Customers
where LastName = 'Smith';
select CustomerId, LastName, FirstName, Phone
from dbo.Customers with (Index=IDX_Customers_LastName_FirstName)
where LastName = 'Smith';