Chapter Summary

You've seen what indexes are and their internal structures. There are two types of indexes: clustered and nonclustered. Clustered indexes sort and store the data rows in the table based on their key values. Clustered indexes are efficient for finding rows because the rows are kept in sorted order on the clustered index key.

KEY TERMS

• index

• clustered index

• nonclustered index

• FILLFACTOR

• rebuilding indexes

• DBCC DBREINDEX

• UPDATE STATISTICS

• indexed view

• Index Tuning Wizard (ITW)

• full-text index

• full-text catalog

Nonclustered indexes have a design entirely separate from the data rows. Rows of nonclustered indexes contain index key values with each index key holding a pointer mapping to a data row containing that index key. The data rows themselves are not stored in the order specified by the nonclustered index definition.

We've also learned about index considerations and how to implement them into your application with the CREATE INDEX statement. The FILLFACTOR argument specifies how full SQL Server should make each page and can be expressed as a percentage from 1 to 100, inclusive. You also learned about the functions involved in renaming and deleting indexes.

We looked at rebuilding indexes to reorganize scattered or fragmented pages. One way of doing this is to delete the original index and then re-create it. This is not favored because it is a two-way process and consumes overhead. Deleting a clustered index and then recreating it means deleting and recreating all nonclustered indexes. The other, one-shot method is to use the DBCC_DBREINDEX statement.

Then we learned about index statistics and how to manage them. Index statistics need to be up to date for the optimizer to decide upon the fastest route of access. Updating statistics involves the use of the UPDATE STATISTICS statement.

We examined the Index Tuning Wizard, a tool that reduces index-tension for many beginners and even professional DBAs. To use Index Tuning Wizard, you have to monitor the default environment by setting up a trace.

Full-text searching was discussed next . Full-text search leverages the power of flexible searches. To set up full-text searches, you first have to run a custom setup and install the Full-Text Search service. (Full-Text Search Service is a program separate from SQL Server 2000.)