SQL Indexes

SQL indexes are used to speed up the retrieval of data rows, from a table or view, to return results for a SQL query.

The two types of indexes that are available are:

Clustered

A clustered index defines the order of data rows in the table or view and for this reason you are only able to have ONE clustered index per table.

A Clustered index is created automatically once a primary key or unique identifier is defined against a table.

Non-Clustered

This type of index contains key values that act as pointers, or row locators, to the actual data rows or clustered index key in the table or view.

It is a completely separate structure from the table or view, therefore requires additional disk space to create, and you can have as many non-clustered indexes as you require on any one table/view.

An example:

Let's consider a library (our table) that contains lots of books (data rows).

When the books arrive at the library they are assigned a number and stored on the shelf in number order. (the clustered index).

A librarian has also created a separate catalog to sorts books alphabetically by author name. This catalog includes the relevant shelf location of the book (non-clustered index).

When a customer walks into the library searching for a book by a particular author (our query),  they would be able to speak to the librarian, who can point them to the exact location on the shelf of the book they are after, relatively quickly.

Clustered or Non Clustered?

The type of index you need to add to your table or view will ultimately depend on what data is stored and the queries you need to run on that data. Whilst each index may speed up data retrieval in some way or another, there may be negative affects as well.

There ismore to SQL server backend performance issues than SQL indexing alone however itis a good place to start when troubleshooting slow SQL queries. BUT REMEMBER,incorrectly used indexes can work against you if you are not careful.