Don't Index Wide Columns
If a table's columns are wide (i.e., the columns have a data type of char(25) or larger), indexes created on those columns might result in a large number of pages, thereby negating the benefit of using an index in the first place. Nonclustered indexes in SQL Server 2000 and 7.0 are structured differently from those in earlier releases. If you're not careful, you can create indexes that take more space on disk than the original data pages. (For more information about changes in index structure and the possible detrimental effect on your database, see "Move Up to SQL Server 2000," by Michelle A. Poolet and Michael D. Reilly, February 2002, InstantDoc ID 23512.)
—Michelle A. Poolet
Solutions by Design
"Indexing Dos and Don'ts"
January 2003, InstantDoc ID 27334
Help the Optimizer Help You
The query optimizer can't do its job well without the right tools and an environment that lets it choose from a broad range of possibilities. Here are a few guidelines to help you supply the right tools:
- Create indexes on frequently joined columns. Clustered indexes enable merge joins and fast lookups with nested-loop joins.
- Create covering indexes on combinations of frequently fetched columns. These indexes satisfy queries from the index's leaf level, and you can use them as input for merge joins if the first index column is also the join column.
- Create indexes with care, especially covering indexes. Indexes improve performance of SELECT queries but degrade performance of modification operations.
- Avoid performing joins on columns with few unique values. These joins can be more costly than table scans, so the query processor might decide not to use them.
- Separate tables that participate in joins onto different disks by using filegroups to exploit parallel disk I/O.
—Kalen Delaney and Itzik Ben-Gan
"Performance Considerations"
December 1999, InstantDoc ID 7524