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

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.

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.

**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 Indexes**

- Clustered Indexes are faster for retrieval and slower for insertion
and update.
- A table can have only one clustered index.
- Don't require extra space to store logical structure.
- Determines the order of storing the data on the disk.

**Non-Clustered Indexes**

- Non-clustered indexes are slower in retrieving data and faster in
insertion and update.

- A table can have multiple non-clustered indexes.

- Require extra space to store logical structure.

- Has no effect of order of storing data on the disk.