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

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.

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.

A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one.

A Non-Clustered Index defines a logical order that does not match the physical order on disk.

A clustered index is essentially a sorted copy of the data in the indexed columns.

The main advantage of a clustered index is that when your query (seek) locates the data in the index then no additional IO is needed to retrieve that data.

The overhead of maintaining a clustered index, especially in a frequently updated table, can lead to poor performance and for that reason it may be preferable to create a non-clustered index.

You might have gone through theory part from the above posts:

-The clustered Index as we can see points directly to record i.e. its direct so it takes less time for a search. Additionally it will not take any extra memory/space to store the index

-While, in non-clustered Index, it indirectly points to the clustered Index then it will access the actual record, due to its indirect nature it will take some what more time to access.Also it needs its own memory/space to store the index

// Copied from MSDN, the second point of non-clustered index is not clearly mentioned in the other answers.

Clustered

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and
  each key value entry has a pointer to the data row that contains the key value.
• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

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.