Showing posts with label nonclustered indexes. Show all posts
Showing posts with label nonclustered indexes. Show all posts

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?

Thursday, May 2, 2013

SQL Indexes and Their Benefits

As the data in your database grows, you may notice that your querying becomes slower.  This occurs because SQL Server needs to search through each record until it finds all the records you are looking for.  So, when this happens to your database, how do we approach the problem?

The answer to this lies in something called indexes.  An index is described as a structure on a table or view that speeds up the retrieval of data.  In SQL Server, there are two major types of indexes:
  1. Clustered Index
    • Clustered indexes sort and store the data based on key values (specified by you).
    • Each table can only contain one clustered index.
  2. Nonclustered index
    • Nonclustered indexes do not sort and store the data like clustered indexes do.  They basically have a pointer to the data based on the key values selected.
    • The pointer is actually called a row locator.
    • Depending on your version of SQL Server, you can have quite a few nonclustered indexes.
When performing a search on table, SQL Server will use the least labor intensive method.  Indexes will be the least labor intensive (finds storage location for the data) than a non-indexed table (table scan), which needs to search through each row to find a match and can be resource intensive.

Now, it is very important to put in time considering what should be included in your indexes.  For example, clustered indexes should be fields that have unique data (primary key).  Nonclustered indexes could be created on other columns that you search on frequently and multiple columns that are searched together could be included on the same nonclustered index.  Be careful, though, because adding too many indexes (one of every column) will cause too much overhead.

Don't be afraid to try out different indexes on your data.  Put an index in place, query the table, and see if your querying improves.  If not, try a different index and keep trying until you feel like you have a couple that fit your needs.