Database Reference
In-Depth Information
Figure 8-1.
Sample
Production.Product
table
The preceding query scanned the entire table since there was no
WHERE
clause. If you need to add a filter through
the
WHERE
clause to retrieve all the products where
StandardCost
is greater than 150, without an index the table will
still have to be scanned, checking the value of
StandardCost
at each row to determine which rows contain a value
greater than 150. An index on the
StandardCost
column could speed up this process by providing a mechanism
that allows a structured search against the data rather than a row-by-row check. You can take two different, and
fundamental, approaches for creating this index.
•
Like a dictionary
: A dictionary is a distinct listing of words in alphabetical order. An index can
be stored in a similar fashion. The data is ordered, although it will still have duplicates. The
first ten rows, ordered by
StandardCost DESC
instead of by
Name
, would look like the data
shown in Figure
8-2
. Notice the
RowNumber
column shows the original placement of the row
when ordering by
Name
.
Figure 8-2.
Product table sorted on
StandardCost
So, now if you wanted to find all the data in the rows where
StandardCost
is greater than 150, the index would
allow you to find them immediately by moving down to the first value greater than 150. An index that applies order to
the data stored based on the index key order is known as a
clustered index.
Because of how SQL Server stores data, this
is one of the most important indexes in your database design. I explain this in detail later in the chapter.
