When starting an application, you should be able to identify many of the indexes based on a reasonable set of rules. As the application grows and changes, the indexes should be reviewed to ensure no good index candidates are overlooked. It should be based on how the application is used not based on theory. In the same light, make sure erroneous, duplicate or valueless indexes are removed. This is a precautionary measure to make certain your SQL Server does not have to manage unneeded indexes. In this tip we will identify index recommendations, index creation and index validation.

Commonly Indexed Columns

A common question for many DBAs and developers, relates to the traditional columns recommended for indexes. The general recommendations are:

  • Primary Key's
  • Foreign Key's
  • Columns supporting these clauses in SELECT, INSERT, UPDATE and DELETE commands:

    • INNER JOIN
    • RIGHT | LEFT OUTER JOIN
    • WHERE
    • ORDER BY
    • GROUP BY
    • HAVING

Additional index considerations should be:

  • The volume of data – With a small number of rows, a table scan may be as fast and cost less than traversing an index to access the data.
  • The selectivity of the data – When the data has low selectivity, i.e. the same data is stored in the column, the value of the index can be minimal.
  • The processing type – With a transaction processing system, the indexes should be minimized to support insertions as
    • Requires Free Membership to View

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.