Tuesday, June 11, 2013

Index Design Tips

I've written previously about SQL Indexes.  I've been reviewing tips on Index Design and came across the following Microsoft site:

General Index Design Guidelines

A few things I thought were interesting in the article:

  • A large number of indexes on one table can affect your Update, Insert and Delete statements.
    • Keep the number of indexes or index columns small on these tables.
    • It may be a good idea to create a view and create indexes on the view rather than on the table that will receive Update, Insert of Delete.  This would allow you to modify your original table, but view the data while using a cluster.
  • Indexing small tables is not always optimal, since it may take longer to search through the indexes than to find the records.
  • Create nonclustered indexes on columns that are referenced frequently in your queries.
    • Order the columns in your index based upon how often those columns are used.  For example, if column c is used more than b and b is used more than a, then the column order in the index should be: c, b, and a.
  • Columns that are referenced in query using exact matches could be a good candidate for clustered or nonclustered indexes.
These are all good pointers and should get any person inexperienced in creating indexes started.  From there, you can usually play around with different indexes until you find the right ones.

What types have you found while creating indexes on your database?

No comments:

Post a Comment