What are the differences between a clustered and a non-clustered index?

Question

Clustered Index

• Only one per table
• Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

• Can be used many times per table
• Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95%.

Clustered indexes physically order the data on the disk. This means no extra data is needed for the index, but there can be only one clustered index (obviously). Accessing data using a clustered index is fastest.

All other indexes must be non-clustered. A non-clustered index has a duplicate of the data from the indexed columns kept ordered together with pointers to the actual data rows (pointers to the clustered index if there is one). This means that accessing data through a non-clustered index has to go through an extra layer of indirection. However if you select only the data that's available in the indexed columns you can get the data back directly from the duplicated index data (that's why it's a good idea to SELECT only the columns that you need and not use *)

Clustered indexes are stored physically on the table. This means they are the fastest and you can only have one clustered index per table.

Non-clustered indexes are stored separately, and you can have as many as you want.

The best option is to set your clustered index on the most used unique column, usually the PK. You should always have a well selected clustered index in your tables, unless a very compelling reason--can't think of a single one, but hey, it may be out there--for not doing so comes up.

Clustered Index

1. There can be only one clustered index for a table.
2. Usually made on the primary key.
3. The leaf nodes of a clustered index contain the data pages.

Non-Clustered Index

1. There can be only 249 non-clustered indexes for a table(till sql version 2005 later versions support upto 999 non-clustered indexes).
2. Usually made on the any key.
3. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Clustered Index

• Only one clustered index can be there in a table
• Sort the records and store them physically according to the order
• Data retrieval is faster than non-clustered indexes
• Do not need extra space to store logical structure

Non Clustered Index

• There can be any number of non-clustered indexes in a table
• Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files
• Data insertion/update is faster than clustered index
• Use extra space to store logical structure

---

Apart from these differences you have to know that when table is non-clustered (when the table doesn't have a clustered index) data files are unordered and it uses Heap data structure as the data structure.

Pros:

Clustered indexes work great for ranges (e.g. select * from my_table where my_key between @min and @max)

In some conditions, the DBMS will not have to do work to sort if you use an orderby statement.

Cons:

Clustered indexes are can slow down inserts because the physical layouts of the records have to be modified as records are put in if the new keys are not in sequential order.

Clustered basically means that the data is in that physical order in the table. This is why you can have only one per table.

Unclustered means it's "only" a logical order.