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.