Database Reference
In-Depth Information
At this point, the table has 1,252,000 rows. The ISBN column is populated with data in the following format:
<Prefix>-<Postfix> with the prefixes from 100 to 600; 2,500 postfixes each.
Let's try to select the data for one of the prefixes, as shown in Listing 2-16.
Listing 2-16.
Nonclustered index usage: Selecting data for the single prefix
-- 2,500 rows
select * from dbo.Books where ISBN like '210%'
As you see in Figure
2-20
, SQL Server decided to use a
Nonclustered Index Seek
with
Key Lookup
as the
execution plan. Selecting 2,500 rows introduces 7,676 logical reads. The clustered index
IDX_Books_BookId
has three
levels, which leads to 7,500 reads during
Key Lookup
operations. The remaining 176 reads were performed on the
nonclustered index when SQL Server traversed the index tree and read pages during a range scan operation.
Figure 2-20.
Selecting data for the single prefix: execution plan
For the next step, let's select the data for five different prefixes. We will run two different selects. In the first one,
we give SQL Server the ability to choose the execution plan as it wishes. In the second select, we force the use of a
nonclustered index with the index hint. The code to accomplish this is shown in Listing 2-17. Figure
2-21
shows the
execution plans.
Listing 2-17.
Nonclustered index usage: Selecting data for five prefixes
-- 12,500 rows
select * from dbo.Books where ISBN like '21[0-4]%'
select * from dbo.Books with (index = IDX_BOOKS_ISBN) where ISBN like '21[0-4]%'
