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.

Clustered and Nonclustered Indexes Described