Database Reference
In-Depth Information
Chapter 9
Index Analysis
In the previous chapter I introduced the concepts surrounding indexes. This chapter takes that information and
adds more functionality. There's a lot of interesting interaction between indexes that you can take advantage of.
There are also a number of settings that affect the behavior of indexes that I didn't address in the preceding chapter.
I'll show you methods to squeeze even more performance out of your system.
In this chapter, I cover the following topics:
•
Advanced indexing techniques
•
Special index types
•
Additional characteristics of indexes
Advanced Indexing Techniques
Here are a few of the more advanced indexing techniques that you can consider:
•
Covering indexes
: These were introduced in Chapter 8.
•
Index intersections
: Use multiple nonclustered indexes to satisfy all the column requirements
(from a table) for a query.
•
Index joins
: Use the index intersection and covering index techniques to avoid hitting the
base table.
•
Filtered indexes
: To be able to index fields with odd data distributions or sparse columns, you
can apply a filter to an index so that it indexes only some data.
•
Indexed views
: These materialize the output of a view on disk.
•
Index compression
: The storage of indexes can be compressed through SQL Server, putting
more rows of data on a page and improving performance.
•
Columnstore indexes
: Instead of grouping and storing data for a row, like traditional indexes,
these indexes group and store based on columns.
I cover these topics in more detail in the following sections.
