Statistics - Pro SQL Server Internals - page 57

('Edward'),('Eddy'),('Emy'),('Frank'),('George'),('Harry'),
('Henry'),('Ida'),('John'),('Jimmy'),('Jenny'),('Jack'),
('Kathy'),('Kim'),('Larry'),('Mary'),('Max'),('Nancy'),
('Olivia'),('Olga'),('Peter'),('Patrick'),('Robert'),
('Ron'),('Steve'),('Shawn'),('Tom'),('Timothy'),
('Uri'),('Vincent')
) Names(Name)
)
,LastNames(LastName)
as
(
select Names.Name
from
(
values('Smith'),('Johnson'),('Williams'),('Jones'),('Brown'),
('Davis'),('Miller'),('Wilson'),('Moore'),('Taylor'),
('Anderson'),('Jackson'),('White'),('Harris')
) Names(Name)
)
insert into dbo.Customers(LastName, FirstName)
select LastName, FirstName
from FirstNames cross join LastNames
go 50
insert into dbo.Customers(LastName, FirstName) values('Isakov','Victor')
go
create nonclustered index IDX_Customers_LastName_FirstName
on dbo.Customers(LastName, FirstName);
Every combination of first and last names specified in the first insert statement has been inserted into the table
50 times. In addition, there is one row, with the first name Victor , inserted by the second insert statement.
Now let's assume that you want to run the query that selects the data based on the FirstName parameter only.
That predicate is not SARGable for the IDX_Customers_LastName_FirstName index because there is no SARGable
predicate on the LastName column, which is the leftmost column in the index.
SQL Server offers two different options on how to execute the query. The first option is to perform a Clustered
Index Scan . The second option is to use a Nonclustered Index Scan doing Key Lookup for every row of the nonclustered
index where the FirstName value matches the parameter.
The nonclustered index row size is much smaller than that of the clustered index. It uses less data pages, and
a scan of the nonclustered index would be more efficient as compared to a clustered index scan, owing to the fewer
I/O reads that it performs. At the same time, the plan with a nonclustered index scan would be less efficient than a
clustered index scan when the table has a large number of rows with a particular FirstName and a large number of key
lookups is required. Unfortunately, the histogram for the IDX_Customers_LastName_FirstName index stores the data
distribution for the LastName column only, and SQL Server does not know about the FirstName data distribution.
Let's run the two selects shown in Listing 3-4 and examine the execution plans in Figure 3-4 .