Chapter 10. Boosting Performance with Indexes

O BJECTIVES

This chapter covers the following Microsoft-specified objectives for the Indexing section of the Database Development SQL Server 2000 exam:

Implement indexing strategies.

• An indexing strategy involves your decisions on how to implement indexes, which columns you choose to index, and how you decide one index is better than another. SQL Server supports two kinds of indexes: clustered and nonclustered. Depending upon the underlying circumstances present in your table structure and your needs, you will have to decide upon one or the other.

Create indexes including clustered and nonclustered, covering index, indexed views.

• Specify index characteristics. Characteristics include clustered, FILLFACTOR , nonclustered, and uniqueness.

• This objective looks at the different characteristics an index can take. Some of these are the index's fill factor, the type of index, and the uniqueness of the index.

Improve index use by using the Index Tuning Wizard, index placement and statistics.

• This objective looks at how to use the Index Tuning Wizard to improve and design accurate, efficient, and proficient indexes on the fly with fascinating results. The Index Tuning Wizard enables you to select and create powerful indexes and statistics for a Microsoft SQL Server 2000 database without prior knowledge of index and database structural designs. The Index Tuning Wizard simplifies one of the tasks that weigh down on DBAs' backs: choosing the correct column to index.

Implementing Indexed Views

• Creating indexes against a view is new to SQL Server 2000. With this functionality comes a few advanced implementations . Pay particular attention to restrictions and required settings, because they are sure to be needed on the exam.

O UTLINE

Overview of Indexing

Indexing and Data Storage

Indexing Architecture

What to Index?

What Not to Index?

Nonclustered Covering Indexes

Creating and Altering Indexes

UNIQUE Indexing

Exploring FILLFACTOR

Altering Existing Indexes

Index Drawbacks

Index Maintenance

Indexing to Improve Performance

Indexing and Statistics

Querying an Index's Statistics

The UPDATE STATISTICS Statement

Dropping Unwanted Indexes

The Index Tuning Wizard

Examining the Index Tuning Wizard

Indexed Views

Indexed View Requirements and Restrictions

Creating Indexed Views

Full-Text Searches

Full-Text Indexes

Administering Full-Text Features

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

• Choosing an indexing strategy can be an extremely hard decision. Know when to implement certain indexes and where to put them. Index choice is definitely going to be questioned on the exam.

• Have a good understanding of indexed views because they are new to SQL Server 2000.

• The indexed view can significantly cut down the time-consuming process of choosing an indexing strategy. Learn how to use it well because it covers a whole objective itself.

• Full-text searches and full-text indexing are topics that might show up on the exam. Because they're not one of the current objectives of the exam, I wouldn't suggest that you concentrate too much study time on them. Always check Microsoft's web site every now and then because objectives are subject to change at any time.

• And finally, practice function syntaxes over and over again and to some extent memorize them.