Databases Reference
In-Depth Information
Figure 11-10.
Effect of adding an index on the TestUsers table
To remove the index scan on TestUserType, you add another index following the previous guidelines. Because no
columns are needed in the
SELECT
clause, you can leave the
INCLUDE
section alone. Here's the index:
CREATE INDEX idx_testusertype_001 ON TestUserType
(UserTypeKey, UserType)
■
although it's minimal, there is a risk associated with adding new indexes in production systems. Certain
routines, especially batch programs, typically depend on data being properly ordered to calculate running sums or carry
out specific tasks. if an index is favored over another to run a query, it's possible for the new index to be used, which has
the potential to change the order in which the data is returned. if all your statements include an
ORDER BY
clause , this
problem won't affect you. But if some of your programs rely on the natural order of records, beware! also, keep in mind
that adding indexes can negatively impact performance by increasing overhead.
Note
Your execution plan should now look like that in Figure
11-11
. Notice that the physical operator has been
changed to a loop. Also notice that the cost of the query has shifted away from the
JOIN
operator: the highest relative
cost (76%) of the plan is spent reading the data from the TestUserType index.
Figure 11-11.
Effect of indexing on the physical operators
