Database Reference
In-Depth Information
LastName is SARGable in both indexes. Both indexes support Index Seek while searching for a particular LastName
value. There is no difference in performance when LastName is the only predicate in the query. Listing 4-5 and
Figure 4-5 illustrates this point.
Listing 4-5. Included vs. key columns: Selecting by LastName only
select CustomerId, LastName, FirstName
from dbo.Customers with (index = IDX_Key)
where LastName = 'Smith';
select CustomerId, LastName, FirstName
from dbo.Customers with (index = IDX_Include)
where LastName = 'Smith';
Figure 4-5. Included vs. key columns: Selecting by LastName only
Nonetheless, the situation changes when you add the FirstName predicate to the queries. With IDX_Key index, a
query is able to do Index Seek using both LastName and FirstName as seek predicates. This would not be possible
with the IDX_Include index. SQL Server needs to scan all rows with a specific LastName and check the predicate
on FirstName column. Listing 4-6 and Figure 4-6 illustrates this point.
 
Search WWH ::




Custom Search