Database Index Guidelines

The following are a few database index guidelines:

• The columns in a WHERE clause of a Transact-SQL query are the primary focus of the query optimizer.

  The column(s) listed in the WHERE clause of a Transact-SQL statement is a possible candidate for an index. For each table, consider creating optional indexes based on the frequency with which the columns are used in WHERE clauses, and take into account the results of the graphical SQL Server Query Analyzer.

• Use single-column indexes.

  Single-column indexes are often more effective than multicolumn indexes. First, they require less space, allowing more index values to fit on one data page. Secondly, the query optimizer can effectively analyze thousands of index-join possibilities. Maintaining a large number of single (or very few)-column indexes provides the query optimizer with more options from which to choose.

• Use clustered indexes.

  The SQL Server Query Analyzer helps you determine which clustered indexes to create. Appropriate use of clustered indexes can significantly improve performance.

For more information on indexing and performance, see SQL Server Books Online and the Microsoft Knowledge Base.