Database Reference
In-Depth Information
Chapter 8
Index Architecture and Behavior
The right index on the right column, or columns, is the basis on which query tuning begins. A missing index or an
index placed on the wrong column, or columns, can be the basis for all performance problems starting with basic
data access, continuing through joins, and ending in filtering clauses. For these reasons, it is extremely important for
everyone—not just a DBA—to understand the different indexing techniques that can be used to optimize the
database design.
In this chapter, I cover the following topics:
•
What an index is
•
The benefits and overhead of an index
•
General recommendations for index design
•
Clustered and nonclustered index behavior and comparisons
•
Recommendations for clustered and nonclustered indexes
What Is an Index?
One of the best ways to reduce disk I/O is to use an index. An index allows SQL Server to find data in a table without
scanning the entire table. An index in a database is analogous to an index in a book. Say, for example, that you
wanted to look up the phrase
table scan
in this topic. In the paper version, without the index at the back of the topic,
you would have to peruse the entire book to find the text you needed. With the index, you know exactly where the
information you want is stored.
While tuning a database for performance, you create indexes on the different columns used in a query to help
SQL Server find data quickly. For example, the following query against the
Production.Product
table results in the
data shown in Figure
8-1
(the first 10 of 500+ rows):
SELECT TOP 10
p.ProductID,
p.[Name],
p.StandardCost,
p.[Weight],
ROW_NUMBER() OVER (ORDER BY p.Name DESC) AS RowNumber
FROM Production.Product p
ORDER BY p.Name DESC;
