Clustered Vs Non-Clustered Index

The difference between the index clustered et Non-clustered in a database is one of the most popular questions in SQL.

Indexes are a very important concept, it makes the execution of your queries fast and if you are comparing a query SELECT which uses an indexed column than one that doesn't, you will see a big difference in performance.

The index is a structure of type B-Tree associated with a table or view:

The index references key values ​​and allows you to find by dichotomy a value determined faster.

• Cluster Index

The Cluster index sort and store data rows based on their column values ​​included in the index definition, in that sense a clustered index is a duplicate of the table. There can only be one index per table because the data rows can only be sorted in one order.

In a clustered index, the internal nodes of the sort tree contain the values ​​of the key (and a RowID), in its physical order, and only the leaf level contains the data pages of the table.

• Non-Cluster Index

The non-clustered index contain the key values ​​of the nonclustered index, and each key value entry has a pointer to the data rows that contain the key value.

The pointer between a row in a nonclustered index and a row of data is called a row locator. The line locator can either be in the form of ROWIDs if there is no clustered index on the table, either by pointing to the key value of the clustered index if it exists.

There can be multiple non-clustered indexes on a table.

A nonclustered index is ordered according to the logical order of its key values ​​(and a KeyID), and its leaf level does not contain the data pages, but the rows of the cluster index.