# Index

An index is a disk-based structure linked to a table or view that facilitates quicker row retrieval. A table or view’s table or view’s columns are used to create keys in an index. These keys are kept in a structure (B-tree) that enables SQL Server to quickly and effectively locate the row or rows that correspond to the key values.

| CLUSTERED INDEX                                                                                          | NON-CLUSTERED INDEX                                                                                                                                        |
| -------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- |
| A clustered index is faster.                                                                             | A non-clustered index is slower.                                                                                                                           |
| The clustered index requires less memory for operations.                                                 | A non-Clustered index requires more memory for operations.                                                                                                 |
| In a clustered index, the clustered index is the main data.                                              | In the Non-Clustered index, the index is the copy of data.                                                                                                 |
| A table can have only one clustered index.                                                               | A table can have multiple non-clustered indexes.                                                                                                           |
| The clustered index has the inherent ability to store data on the disk.                                  | A non-Clustered index does not have the inherent ability to store data on the disk.                                                                        |
| Clustered index store pointers to block not data.                                                        | Non-Clustered index storescontainThe non-Clustered both value and a pointer to the actual row that holds data.                                             |
| In Clustered index leaf nodes are actual data itself.                                                    | In Non-Clustered index leaf nodes are not the actual data itself rather they only contain included columns.                                                |
| In a Clustered index, Clustered key defines the order of data within a table.                            | In a Non-Clustered index, the index key defines the order of data within the index.                                                                        |
| A Clustered index is a type of index in which table records are physically reordered to match the index. | A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on the disk. |
| The size of The primary clustered index is large.                                                        | The size of the non-clustered index is compared relativelyThe composite is smaller.                                                                        |
| Primary Keys of the table by default are clustered indexes.                                              | The composite key when used with unique constraints of the table act as the non-clustered index.                                                           |