Databases Reference
In-Depth Information
Let's quickly review a simplified syntax to create an index:
CREATE INDEX [
index_name
] ON [
table_name
]
(
col1
,
col2
...)
INCLUDE (
col3
,
col4
...)
Although creating an index is simple, it's important to verify that it's being used by SQL Database and that it has
the desired effect. To continue the previous example (from Figure
11-2
), you can create an index on the TestUsers
table. But first, let's back up and review a few things about the table and the statement you wish to optimize.
It's important to realize that the columns included in the first part of the index are used as a key when searching
for data in the index and when joining tables. And because it's acceptable to have multiple columns as part of the
key, their order is absolutely critical! At times, you pick the columns that are included in the
WHERE
clause first and
then those that are part of the
JOIN
. You may find, however, that sometimes it's best to start with the
JOIN
columns;
this depends on your statement and the data at hand. You will need to try both approaches to determine which one
works for you. Next come the columns in the
INCLUDE
section of the
CREATE INDEX
command; these columns are
here for only one reason: they help avoid a lookup into the primary table for data that is needed by the
SELECT
clause.
Performing lookups isn't always a performance issue, but it can become an issue if you have a lookup operation in a
large batch of records.
■
Note
an index that contains all the columns needed to serve a query is called a
covering index
.
If you dissect the previous
SELECT
query, you obtain the columns in the following list (in order of placement in
the index) that belong to the TestUsers table:
WHERE
. Contains the AgeGroup field from TestUsers.
•
JOIN
. Contains the UserType field from TestUsers.
•
SELECT
. Contains the Name and UserType fields. The UserType column is already part of the
JOIN
clause; there is no need to count it twice.
•
Let's begin by creating an index that looks like this:
CREATE INDEX idx_testusers_001 ON TestUsers
(AgeGroup, UserType)
INCLUDE (Name)
Running the statement now yields the execution plan shown in Figure
11-10
. This is a better plan than the one in
Figure
11-2
because SQL Database is performing an index seek instead of an index scan. Using an index seek means
SQL Database is able to locate the first record needed by the statement very quickly. However, you still have an index
scan on the TestUserType table. Let's fix that.
