INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Index Choices

How do I know what type of index to create? by TomSark
Posted: 8 Jan 01

We get a lot of index questions related to what type of index should be created given a particular situation.  I compiled the items below from books-online to facilitate this discussion:

Before creating clustered indexes, understand how your data will be accessed.

Consider using a clustered index for:
* Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes. However, if there are very few distinct values, such as only 1 and 0, no index should be created.
* Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
* Columns that are accessed sequentially.
* Queries that return large result sets.
* Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
* OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.

Clustered indexes are not a good choice for:
* Columns that undergo frequent changes because this results in the entire row moving (because SQL Server must keep the rowÆs data values in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
* Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.

Before creating nonclustered indexes, understand how your data will be accessed.

Consider using nonclustered indexes for:
* Columns that contain a high number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, no index should be created.
* Queries that do not return large result sets.
* Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.
* Decision Support System applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
* Covered queries.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close