MCSE Training Guide: SQL Server 7 Database Design

Chapter 4: Indexing

REVIEW BREAK

Index Types

In this last section you have seen different types of indexes that SQL Server supports. The most important distinction to an index is whether it is clustered or non-clustered. The clustering type affects the very structure of the index, whereas the composite and unique types simply describe details of the index. Remember that clustered indexes physically order the data of the table itself, which is why there can only be one per table. Non-clustered indexes merely point to the actual rows either through direct pointers, or by storing the clustered key value if one exists. Composite indexes are indexes which are made on more than one column, and unique indexes provide automatic checking that the indexed column or columns values are unique.

Now that you understand the types of indexes that you can create, you are ready to explore some theory on how to decide which indexes to use in different situations. Then you will be ready to look at how to actually implement indexes in your database.

GUIDELINES FOR INDEXING

• Create and maintain indexes.
  • Choose an indexing strategy that will optimize performance.
  • Given a situation, choose the appropriate type of index to create.
  • Choose the column or columns to index.
  • Choose the appropriate index characteristics, specifically FILLFACTOR, DROP_EXISTING, and PAD INDEX.

There are no hard and fast rules for when you should or should not create an index, but there are some pretty good rules of thumb that you can follow. First, you should create indexes only for those columns that are used in queries. The lname column in the employee table might be a good candidate for an index, but if it is never used (or very infrequently used) in queries it should not be indexed.

Second, indexes should be chosen that are selective. SQL Server works best with indexes that narrow its search quickly. For example, if you were to index a table containing a listing of Kansas City's residents, indexing on last name would probably be a good idea. Even for a common name like Jones, the number of rows returned (perhaps 25,000) would be small in comparison to the total number of rows in the table (perhaps 1.3 million). The selectivity in this example is around 2 percent - finding all people with the surname Jones returns only about 2 percent of the table.

On the other hand, indexing on a column that is not very unique (such as gender) is useless to SQL Server. If we assume that Kansas City's distribution of women and men is roughly equal, then about half of the table will be returned if SQL Server tries to narrow its search with the index on gender.

Choosing Which Columns to Index

Here are some guidelines for choosing which columns to include in an index:

• Primary keys and foreign keys should nearly always be indexed. This provides SQL Server with the most number of methods to perform the join.
• Create indexes on columns that you often search on-in other words, columns often used in the WHERE clause of a query.
• Create indexes that cover the query. This is an index in which all the data usually retrieved from a query is held within the composite index.
• Create indexes on columns that are often searched for a range of values, like datetime fields.
• Create indexes on columns that are often used to sort the results of a query (those columns often included in an ORDER BY clause).

Knowing which columns to index is important. Equally important is knowing what type of index to create.

Choosing an Index Type

Keep the following in mind when you are choosing the type of index you will be creating:

• Create unique indexes on a primary key or alternate key. This allows SQL Server to enforce entity integrity.

  (If you create PRIMARY KEY or UNIQUE constraints on a table as described in Chapter 3, SQL Server creates a unique Index for you.)

• If you often retrieve data in sorted order by a particular column, consider putting a clustered index on that column. Because a clustered index physically sorts the rows in a table, SQL Server can retrieve rows very efficiently. The clustered index is most efficient for range queries and least useful when applied to simple IDENTITY columns.
• If two or more columns are often searched as a unit, consider placing a composite index on both columns.
• Consider using a non-clustered index on the primary key of a table if the table uses an IDENTITY column as its primary key. Placing a clustered index on an IDENTITY column causes all new rows to be inserted at the end of the table, which may cause concurrency problems if many users are inserting data into the table. Even with row-level locking, excessive insert activity on one data page will cause problems.

Indexes can be created or removed without affecting the data in a table, so feel free to experiment with different types of indexes if you need to.

MANAGING INDEXES WITH TRANSACT-SQL

• Create and maintain indexes.
  • Choose an indexing strategy that will optimize performance.
  • Given a situation, choose the appropriate type of index to create.
  • Choose the column or columns to index.
  • Choose the appropriate index characteristics, specifically FILLFACTOR, DROP_EXISTING, and PAD INDEX.

Part of the database designer's role in database implementation is managing the indexes on the database. One of the nicer features of SQL Server is that indexes may be created and dropped independently of their tables, so indexes may be changed without affecting the data in the database. Typically, these changes are accomplished through the use of the Transact-SQL statements covered in this section....