Every time that I write a CREATE TABLE SQL command and there is an index in the sentece the same doubt assault me. What is the difference between a clustered index and a non-clustered one?

To understand this first I have to explain what is an index. Indexs are database objects that helps to find records inside the database. As the data in the database tables grows a mechanism is needed to improve the information search performance. Here is when the indexs comes to help in searchs. Indexs have an structure like a tree, instead of doing a sequential read of the table we can jump between nodes in the index to arrive to the desired data. This way there are less reads to disk and the result is obtained faster.

Now that we know what is an index is time to see the diference between them. Let's start with a clustered one. In a clustered index data is stored in the leaves of the tree, so when we arrive at a leave that we are searching for data is already there, available to collect.

On the other hand non-clustered indexs doesn't have the data on the leaves, they have a pointer to the data, then it is necessary to do a bookmark lookup to actually get the data.

Obviously non-clustered indexs are slower than clustered ones, they have to perform an extra step. But in a table you can only have one clustered index (there's only one way to store data) and you can have multiple non-clustered indexs. From SQL Server 2005 onwards theres the possibility to stored certain data columns in a non-clustered indexs avoiding the bookmark lookup, there's an article on the TechNet that explains that.

There are some points to consider regarding indexs:

• Identity columns that are primary key (PK) usually belongs to the clustered index of the table. This is done to avoid page split.
• If certain searches repeat often then is recommended to create a non-clustered index. If the result columns are just a few they can be included in the index.
• If searches are done by multiple columns then a non-clustered index is recommended.