SQL Server indexes made handy

Jeremy Kadlec of Edgewood Solutions

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

      Login

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      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.

      Back to top